1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61COLUMN_PARTS = ("this", "table", "db", "catalog")
  62
  63
  64class Expression(metaclass=_Expression):
  65    """
  66    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  67    context, such as its child expressions, their names (arg keys), and whether a given child expression
  68    is optional or not.
  69
  70    Attributes:
  71        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  72            and representing expressions as strings.
  73        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  74            arg keys to booleans that indicate whether the corresponding args are optional.
  75        parent: a reference to the parent expression (or None, in case of root expressions).
  76        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  77            uses to refer to it.
  78        index: the index of an expression if it is inside of a list argument in its parent.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83        meta: a dictionary that can be used to store useful metadata for a given expression.
  84
  85    Example:
  86        >>> class Foo(Expression):
  87        ...     arg_types = {"this": True, "expression": False}
  88
  89        The above definition informs us that Foo is an Expression that requires an argument called
  90        "this" and may also optionally receive an argument called "expression".
  91
  92    Args:
  93        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  94    """
  95
  96    key = "expression"
  97    arg_types = {"this": True}
  98    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  99
 100    def __init__(self, **args: t.Any):
 101        self.args: t.Dict[str, t.Any] = args
 102        self.parent: t.Optional[Expression] = None
 103        self.arg_key: t.Optional[str] = None
 104        self.index: t.Optional[int] = None
 105        self.comments: t.Optional[t.List[str]] = None
 106        self._type: t.Optional[DataType] = None
 107        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 108        self._hash: t.Optional[int] = None
 109
 110        for arg_key, value in self.args.items():
 111            self._set_parent(arg_key, value)
 112
 113    def __eq__(self, other) -> bool:
 114        return type(self) is type(other) and hash(self) == hash(other)
 115
 116    @property
 117    def hashable_args(self) -> t.Any:
 118        return frozenset(
 119            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 120            for k, v in self.args.items()
 121            if not (v is None or v is False or (type(v) is list and not v))
 122        )
 123
 124    def __hash__(self) -> int:
 125        if self._hash is not None:
 126            return self._hash
 127
 128        return hash((self.__class__, self.hashable_args))
 129
 130    @property
 131    def this(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "this".
 134        """
 135        return self.args.get("this")
 136
 137    @property
 138    def expression(self) -> t.Any:
 139        """
 140        Retrieves the argument with key "expression".
 141        """
 142        return self.args.get("expression")
 143
 144    @property
 145    def expressions(self) -> t.List[t.Any]:
 146        """
 147        Retrieves the argument with key "expressions".
 148        """
 149        return self.args.get("expressions") or []
 150
 151    def text(self, key) -> str:
 152        """
 153        Returns a textual representation of the argument corresponding to "key". This can only be used
 154        for args that are strings or leaf Expression instances, such as identifiers and literals.
 155        """
 156        field = self.args.get(key)
 157        if isinstance(field, str):
 158            return field
 159        if isinstance(field, (Identifier, Literal, Var)):
 160            return field.this
 161        if isinstance(field, (Star, Null)):
 162            return field.name
 163        return ""
 164
 165    @property
 166    def is_string(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a string.
 169        """
 170        return isinstance(self, Literal) and self.args["is_string"]
 171
 172    @property
 173    def is_number(self) -> bool:
 174        """
 175        Checks whether a Literal expression is a number.
 176        """
 177        return isinstance(self, Literal) and not self.args["is_string"]
 178
 179    @property
 180    def is_negative(self) -> bool:
 181        """
 182        Checks whether an expression is negative.
 183
 184        Handles both exp.Neg and Literal numbers with "-" which come from optimizer.simplify.
 185        """
 186        return isinstance(self, Neg) or (self.is_number and self.this.startswith("-"))
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether a Literal expression is an integer.
 192        """
 193        return self.is_number and is_int(self.name)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]] = None) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307
 308        if comments:
 309            for comment in comments:
 310                _, *meta = comment.split(SQLGLOT_META)
 311                if meta:
 312                    for kv in "".join(meta).split(","):
 313                        k, *v = kv.split("=")
 314                        value = v[0].strip() if v else True
 315                        self.meta[k.strip()] = value
 316                self.comments.append(comment)
 317
 318    def pop_comments(self) -> t.List[str]:
 319        comments = self.comments or []
 320        self.comments = None
 321        return comments
 322
 323    def append(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Appends value to arg_key if it's a list or sets it as a new list.
 326
 327        Args:
 328            arg_key (str): name of the list expression arg
 329            value (Any): value to append to the list
 330        """
 331        if type(self.args.get(arg_key)) is not list:
 332            self.args[arg_key] = []
 333        self._set_parent(arg_key, value)
 334        values = self.args[arg_key]
 335        if hasattr(value, "parent"):
 336            value.index = len(values)
 337        values.append(value)
 338
 339    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 340        """
 341        Sets arg_key to value.
 342
 343        Args:
 344            arg_key: name of the expression arg.
 345            value: value to set the arg to.
 346            index: if the arg is a list, this specifies what position to add the value in it.
 347        """
 348        if index is not None:
 349            expressions = self.args.get(arg_key) or []
 350
 351            if seq_get(expressions, index) is None:
 352                return
 353            if value is None:
 354                expressions.pop(index)
 355                for v in expressions[index:]:
 356                    v.index = v.index - 1
 357                return
 358
 359            if isinstance(value, list):
 360                expressions.pop(index)
 361                expressions[index:index] = value
 362            else:
 363                expressions[index] = value
 364
 365            value = expressions
 366        elif value is None:
 367            self.args.pop(arg_key, None)
 368            return
 369
 370        self.args[arg_key] = value
 371        self._set_parent(arg_key, value, index)
 372
 373    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 374        if hasattr(value, "parent"):
 375            value.parent = self
 376            value.arg_key = arg_key
 377            value.index = index
 378        elif type(value) is list:
 379            for index, v in enumerate(value):
 380                if hasattr(v, "parent"):
 381                    v.parent = self
 382                    v.arg_key = arg_key
 383                    v.index = index
 384
 385    @property
 386    def depth(self) -> int:
 387        """
 388        Returns the depth of this tree.
 389        """
 390        if self.parent:
 391            return self.parent.depth + 1
 392        return 0
 393
 394    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 395        """Yields the key and expression for all arguments, exploding list args."""
 396        # remove tuple when python 3.7 is deprecated
 397        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 398            if type(vs) is list:
 399                for v in reversed(vs) if reverse else vs:
 400                    if hasattr(v, "parent"):
 401                        yield v
 402            else:
 403                if hasattr(vs, "parent"):
 404                    yield vs
 405
 406    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 407        """
 408        Returns the first node in this tree which matches at least one of
 409        the specified types.
 410
 411        Args:
 412            expression_types: the expression type(s) to match.
 413            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 414
 415        Returns:
 416            The node which matches the criteria or None if no such node was found.
 417        """
 418        return next(self.find_all(*expression_types, bfs=bfs), None)
 419
 420    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 421        """
 422        Returns a generator object which visits all nodes in this tree and only
 423        yields those that match at least one of the specified expression types.
 424
 425        Args:
 426            expression_types: the expression type(s) to match.
 427            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 428
 429        Returns:
 430            The generator object.
 431        """
 432        for expression in self.walk(bfs=bfs):
 433            if isinstance(expression, expression_types):
 434                yield expression
 435
 436    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 437        """
 438        Returns a nearest parent matching expression_types.
 439
 440        Args:
 441            expression_types: the expression type(s) to match.
 442
 443        Returns:
 444            The parent node.
 445        """
 446        ancestor = self.parent
 447        while ancestor and not isinstance(ancestor, expression_types):
 448            ancestor = ancestor.parent
 449        return ancestor  # type: ignore
 450
 451    @property
 452    def parent_select(self) -> t.Optional[Select]:
 453        """
 454        Returns the parent select statement.
 455        """
 456        return self.find_ancestor(Select)
 457
 458    @property
 459    def same_parent(self) -> bool:
 460        """Returns if the parent is the same class as itself."""
 461        return type(self.parent) is self.__class__
 462
 463    def root(self) -> Expression:
 464        """
 465        Returns the root expression of this tree.
 466        """
 467        expression = self
 468        while expression.parent:
 469            expression = expression.parent
 470        return expression
 471
 472    def walk(
 473        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 474    ) -> t.Iterator[Expression]:
 475        """
 476        Returns a generator object which visits all nodes in this tree.
 477
 478        Args:
 479            bfs: if set to True the BFS traversal order will be applied,
 480                otherwise the DFS traversal will be used instead.
 481            prune: callable that returns True if the generator should stop traversing
 482                this branch of the tree.
 483
 484        Returns:
 485            the generator object.
 486        """
 487        if bfs:
 488            yield from self.bfs(prune=prune)
 489        else:
 490            yield from self.dfs(prune=prune)
 491
 492    def dfs(
 493        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 494    ) -> t.Iterator[Expression]:
 495        """
 496        Returns a generator object which visits all nodes in this tree in
 497        the DFS (Depth-first) order.
 498
 499        Returns:
 500            The generator object.
 501        """
 502        stack = [self]
 503
 504        while stack:
 505            node = stack.pop()
 506
 507            yield node
 508
 509            if prune and prune(node):
 510                continue
 511
 512            for v in node.iter_expressions(reverse=True):
 513                stack.append(v)
 514
 515    def bfs(
 516        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 517    ) -> t.Iterator[Expression]:
 518        """
 519        Returns a generator object which visits all nodes in this tree in
 520        the BFS (Breadth-first) order.
 521
 522        Returns:
 523            The generator object.
 524        """
 525        queue = deque([self])
 526
 527        while queue:
 528            node = queue.popleft()
 529
 530            yield node
 531
 532            if prune and prune(node):
 533                continue
 534
 535            for v in node.iter_expressions():
 536                queue.append(v)
 537
 538    def unnest(self):
 539        """
 540        Returns the first non parenthesis child or self.
 541        """
 542        expression = self
 543        while type(expression) is Paren:
 544            expression = expression.this
 545        return expression
 546
 547    def unalias(self):
 548        """
 549        Returns the inner expression if this is an Alias.
 550        """
 551        if isinstance(self, Alias):
 552            return self.this
 553        return self
 554
 555    def unnest_operands(self):
 556        """
 557        Returns unnested operands as a tuple.
 558        """
 559        return tuple(arg.unnest() for arg in self.iter_expressions())
 560
 561    def flatten(self, unnest=True):
 562        """
 563        Returns a generator which yields child nodes whose parents are the same class.
 564
 565        A AND B AND C -> [A, B, C]
 566        """
 567        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 568            if type(node) is not self.__class__:
 569                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 570
 571    def __str__(self) -> str:
 572        return self.sql()
 573
 574    def __repr__(self) -> str:
 575        return _to_s(self)
 576
 577    def to_s(self) -> str:
 578        """
 579        Same as __repr__, but includes additional information which can be useful
 580        for debugging, like empty or missing args and the AST nodes' object IDs.
 581        """
 582        return _to_s(self, verbose=True)
 583
 584    def sql(self, dialect: DialectType = None, **opts) -> str:
 585        """
 586        Returns SQL string representation of this tree.
 587
 588        Args:
 589            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 590            opts: other `sqlglot.generator.Generator` options.
 591
 592        Returns:
 593            The SQL string.
 594        """
 595        from sqlglot.dialects import Dialect
 596
 597        return Dialect.get_or_raise(dialect).generate(self, **opts)
 598
 599    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 600        """
 601        Visits all tree nodes (excluding already transformed ones)
 602        and applies the given transformation function to each node.
 603
 604        Args:
 605            fun: a function which takes a node as an argument and returns a
 606                new transformed node or the same node without modifications. If the function
 607                returns None, then the corresponding node will be removed from the syntax tree.
 608            copy: if set to True a new tree instance is constructed, otherwise the tree is
 609                modified in place.
 610
 611        Returns:
 612            The transformed tree.
 613        """
 614        root = None
 615        new_node = None
 616
 617        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 618            parent, arg_key, index = node.parent, node.arg_key, node.index
 619            new_node = fun(node, *args, **kwargs)
 620
 621            if not root:
 622                root = new_node
 623            elif new_node is not node:
 624                parent.set(arg_key, new_node, index)
 625
 626        assert root
 627        return root.assert_is(Expression)
 628
 629    @t.overload
 630    def replace(self, expression: E) -> E: ...
 631
 632    @t.overload
 633    def replace(self, expression: None) -> None: ...
 634
 635    def replace(self, expression):
 636        """
 637        Swap out this expression with a new expression.
 638
 639        For example::
 640
 641            >>> tree = Select().select("x").from_("tbl")
 642            >>> tree.find(Column).replace(column("y"))
 643            Column(
 644              this=Identifier(this=y, quoted=False))
 645            >>> tree.sql()
 646            'SELECT y FROM tbl'
 647
 648        Args:
 649            expression: new node
 650
 651        Returns:
 652            The new expression or expressions.
 653        """
 654        parent = self.parent
 655
 656        if not parent or parent is expression:
 657            return expression
 658
 659        key = self.arg_key
 660        value = parent.args.get(key)
 661
 662        if type(expression) is list and isinstance(value, Expression):
 663            # We are trying to replace an Expression with a list, so it's assumed that
 664            # the intention was to really replace the parent of this expression.
 665            value.parent.replace(expression)
 666        else:
 667            parent.set(key, expression, self.index)
 668
 669        if expression is not self:
 670            self.parent = None
 671            self.arg_key = None
 672            self.index = None
 673
 674        return expression
 675
 676    def pop(self: E) -> E:
 677        """
 678        Remove this expression from its AST.
 679
 680        Returns:
 681            The popped expression.
 682        """
 683        self.replace(None)
 684        return self
 685
 686    def assert_is(self, type_: t.Type[E]) -> E:
 687        """
 688        Assert that this `Expression` is an instance of `type_`.
 689
 690        If it is NOT an instance of `type_`, this raises an assertion error.
 691        Otherwise, this returns this expression.
 692
 693        Examples:
 694            This is useful for type security in chained expressions:
 695
 696            >>> import sqlglot
 697            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 698            'SELECT x, z FROM y'
 699        """
 700        if not isinstance(self, type_):
 701            raise AssertionError(f"{self} is not {type_}.")
 702        return self
 703
 704    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 705        """
 706        Checks if this expression is valid (e.g. all mandatory args are set).
 707
 708        Args:
 709            args: a sequence of values that were used to instantiate a Func expression. This is used
 710                to check that the provided arguments don't exceed the function argument limit.
 711
 712        Returns:
 713            A list of error messages for all possible errors that were found.
 714        """
 715        errors: t.List[str] = []
 716
 717        for k in self.args:
 718            if k not in self.arg_types:
 719                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 720        for k, mandatory in self.arg_types.items():
 721            v = self.args.get(k)
 722            if mandatory and (v is None or (isinstance(v, list) and not v)):
 723                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 724
 725        if (
 726            args
 727            and isinstance(self, Func)
 728            and len(args) > len(self.arg_types)
 729            and not self.is_var_len_args
 730        ):
 731            errors.append(
 732                f"The number of provided arguments ({len(args)}) is greater than "
 733                f"the maximum number of supported arguments ({len(self.arg_types)})"
 734            )
 735
 736        return errors
 737
 738    def dump(self):
 739        """
 740        Dump this Expression to a JSON-serializable dict.
 741        """
 742        from sqlglot.serde import dump
 743
 744        return dump(self)
 745
 746    @classmethod
 747    def load(cls, obj):
 748        """
 749        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 750        """
 751        from sqlglot.serde import load
 752
 753        return load(obj)
 754
 755    def and_(
 756        self,
 757        *expressions: t.Optional[ExpOrStr],
 758        dialect: DialectType = None,
 759        copy: bool = True,
 760        **opts,
 761    ) -> Condition:
 762        """
 763        AND this condition with one or multiple expressions.
 764
 765        Example:
 766            >>> condition("x=1").and_("y=1").sql()
 767            'x = 1 AND y = 1'
 768
 769        Args:
 770            *expressions: the SQL code strings to parse.
 771                If an `Expression` instance is passed, it will be used as-is.
 772            dialect: the dialect used to parse the input expression.
 773            copy: whether to copy the involved expressions (only applies to Expressions).
 774            opts: other options to use to parse the input expressions.
 775
 776        Returns:
 777            The new And condition.
 778        """
 779        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 780
 781    def or_(
 782        self,
 783        *expressions: t.Optional[ExpOrStr],
 784        dialect: DialectType = None,
 785        copy: bool = True,
 786        **opts,
 787    ) -> Condition:
 788        """
 789        OR this condition with one or multiple expressions.
 790
 791        Example:
 792            >>> condition("x=1").or_("y=1").sql()
 793            'x = 1 OR y = 1'
 794
 795        Args:
 796            *expressions: the SQL code strings to parse.
 797                If an `Expression` instance is passed, it will be used as-is.
 798            dialect: the dialect used to parse the input expression.
 799            copy: whether to copy the involved expressions (only applies to Expressions).
 800            opts: other options to use to parse the input expressions.
 801
 802        Returns:
 803            The new Or condition.
 804        """
 805        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 806
 807    def not_(self, copy: bool = True):
 808        """
 809        Wrap this condition with NOT.
 810
 811        Example:
 812            >>> condition("x=1").not_().sql()
 813            'NOT x = 1'
 814
 815        Args:
 816            copy: whether to copy this object.
 817
 818        Returns:
 819            The new Not instance.
 820        """
 821        return not_(self, copy=copy)
 822
 823    def as_(
 824        self,
 825        alias: str | Identifier,
 826        quoted: t.Optional[bool] = None,
 827        dialect: DialectType = None,
 828        copy: bool = True,
 829        **opts,
 830    ) -> Alias:
 831        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 832
 833    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 834        this = self.copy()
 835        other = convert(other, copy=True)
 836        if not isinstance(this, klass) and not isinstance(other, klass):
 837            this = _wrap(this, Binary)
 838            other = _wrap(other, Binary)
 839        if reverse:
 840            return klass(this=other, expression=this)
 841        return klass(this=this, expression=other)
 842
 843    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 844        return Bracket(
 845            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 846        )
 847
 848    def __iter__(self) -> t.Iterator:
 849        if "expressions" in self.arg_types:
 850            return iter(self.args.get("expressions") or [])
 851        # We define this because __getitem__ converts Expression into an iterable, which is
 852        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 853        # See: https://peps.python.org/pep-0234/
 854        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 855
 856    def isin(
 857        self,
 858        *expressions: t.Any,
 859        query: t.Optional[ExpOrStr] = None,
 860        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 861        copy: bool = True,
 862        **opts,
 863    ) -> In:
 864        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 865        if subquery and not isinstance(subquery, Subquery):
 866            subquery = subquery.subquery(copy=False)
 867
 868        return In(
 869            this=maybe_copy(self, copy),
 870            expressions=[convert(e, copy=copy) for e in expressions],
 871            query=subquery,
 872            unnest=(
 873                Unnest(
 874                    expressions=[
 875                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 876                        for e in ensure_list(unnest)
 877                    ]
 878                )
 879                if unnest
 880                else None
 881            ),
 882        )
 883
 884    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 885        return Between(
 886            this=maybe_copy(self, copy),
 887            low=convert(low, copy=copy, **opts),
 888            high=convert(high, copy=copy, **opts),
 889        )
 890
 891    def is_(self, other: ExpOrStr) -> Is:
 892        return self._binop(Is, other)
 893
 894    def like(self, other: ExpOrStr) -> Like:
 895        return self._binop(Like, other)
 896
 897    def ilike(self, other: ExpOrStr) -> ILike:
 898        return self._binop(ILike, other)
 899
 900    def eq(self, other: t.Any) -> EQ:
 901        return self._binop(EQ, other)
 902
 903    def neq(self, other: t.Any) -> NEQ:
 904        return self._binop(NEQ, other)
 905
 906    def rlike(self, other: ExpOrStr) -> RegexpLike:
 907        return self._binop(RegexpLike, other)
 908
 909    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 910        div = self._binop(Div, other)
 911        div.args["typed"] = typed
 912        div.args["safe"] = safe
 913        return div
 914
 915    def asc(self, nulls_first: bool = True) -> Ordered:
 916        return Ordered(this=self.copy(), nulls_first=nulls_first)
 917
 918    def desc(self, nulls_first: bool = False) -> Ordered:
 919        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 920
 921    def __lt__(self, other: t.Any) -> LT:
 922        return self._binop(LT, other)
 923
 924    def __le__(self, other: t.Any) -> LTE:
 925        return self._binop(LTE, other)
 926
 927    def __gt__(self, other: t.Any) -> GT:
 928        return self._binop(GT, other)
 929
 930    def __ge__(self, other: t.Any) -> GTE:
 931        return self._binop(GTE, other)
 932
 933    def __add__(self, other: t.Any) -> Add:
 934        return self._binop(Add, other)
 935
 936    def __radd__(self, other: t.Any) -> Add:
 937        return self._binop(Add, other, reverse=True)
 938
 939    def __sub__(self, other: t.Any) -> Sub:
 940        return self._binop(Sub, other)
 941
 942    def __rsub__(self, other: t.Any) -> Sub:
 943        return self._binop(Sub, other, reverse=True)
 944
 945    def __mul__(self, other: t.Any) -> Mul:
 946        return self._binop(Mul, other)
 947
 948    def __rmul__(self, other: t.Any) -> Mul:
 949        return self._binop(Mul, other, reverse=True)
 950
 951    def __truediv__(self, other: t.Any) -> Div:
 952        return self._binop(Div, other)
 953
 954    def __rtruediv__(self, other: t.Any) -> Div:
 955        return self._binop(Div, other, reverse=True)
 956
 957    def __floordiv__(self, other: t.Any) -> IntDiv:
 958        return self._binop(IntDiv, other)
 959
 960    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 961        return self._binop(IntDiv, other, reverse=True)
 962
 963    def __mod__(self, other: t.Any) -> Mod:
 964        return self._binop(Mod, other)
 965
 966    def __rmod__(self, other: t.Any) -> Mod:
 967        return self._binop(Mod, other, reverse=True)
 968
 969    def __pow__(self, other: t.Any) -> Pow:
 970        return self._binop(Pow, other)
 971
 972    def __rpow__(self, other: t.Any) -> Pow:
 973        return self._binop(Pow, other, reverse=True)
 974
 975    def __and__(self, other: t.Any) -> And:
 976        return self._binop(And, other)
 977
 978    def __rand__(self, other: t.Any) -> And:
 979        return self._binop(And, other, reverse=True)
 980
 981    def __or__(self, other: t.Any) -> Or:
 982        return self._binop(Or, other)
 983
 984    def __ror__(self, other: t.Any) -> Or:
 985        return self._binop(Or, other, reverse=True)
 986
 987    def __neg__(self) -> Neg:
 988        return Neg(this=_wrap(self.copy(), Binary))
 989
 990    def __invert__(self) -> Not:
 991        return not_(self.copy())
 992
 993
 994IntoType = t.Union[
 995    str,
 996    t.Type[Expression],
 997    t.Collection[t.Union[str, t.Type[Expression]]],
 998]
 999ExpOrStr = t.Union[str, Expression]
1000
1001
1002class Condition(Expression):
1003    """Logical conditions like x AND y, or simply x"""
1004
1005
1006class Predicate(Condition):
1007    """Relationships like x = y, x > 1, x >= y."""
1008
1009
1010class DerivedTable(Expression):
1011    @property
1012    def selects(self) -> t.List[Expression]:
1013        return self.this.selects if isinstance(self.this, Query) else []
1014
1015    @property
1016    def named_selects(self) -> t.List[str]:
1017        return [select.output_name for select in self.selects]
1018
1019
1020class Query(Expression):
1021    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1022        """
1023        Returns a `Subquery` that wraps around this query.
1024
1025        Example:
1026            >>> subquery = Select().select("x").from_("tbl").subquery()
1027            >>> Select().select("x").from_(subquery).sql()
1028            'SELECT x FROM (SELECT x FROM tbl)'
1029
1030        Args:
1031            alias: an optional alias for the subquery.
1032            copy: if `False`, modify this expression instance in-place.
1033        """
1034        instance = maybe_copy(self, copy)
1035        if not isinstance(alias, Expression):
1036            alias = TableAlias(this=to_identifier(alias)) if alias else None
1037
1038        return Subquery(this=instance, alias=alias)
1039
1040    def limit(
1041        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1042    ) -> Q:
1043        """
1044        Adds a LIMIT clause to this query.
1045
1046        Example:
1047            >>> select("1").union(select("1")).limit(1).sql()
1048            'SELECT 1 UNION SELECT 1 LIMIT 1'
1049
1050        Args:
1051            expression: the SQL code string to parse.
1052                This can also be an integer.
1053                If a `Limit` instance is passed, it will be used as-is.
1054                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1055            dialect: the dialect used to parse the input expression.
1056            copy: if `False`, modify this expression instance in-place.
1057            opts: other options to use to parse the input expressions.
1058
1059        Returns:
1060            A limited Select expression.
1061        """
1062        return _apply_builder(
1063            expression=expression,
1064            instance=self,
1065            arg="limit",
1066            into=Limit,
1067            prefix="LIMIT",
1068            dialect=dialect,
1069            copy=copy,
1070            into_arg="expression",
1071            **opts,
1072        )
1073
1074    def offset(
1075        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1076    ) -> Q:
1077        """
1078        Set the OFFSET expression.
1079
1080        Example:
1081            >>> Select().from_("tbl").select("x").offset(10).sql()
1082            'SELECT x FROM tbl OFFSET 10'
1083
1084        Args:
1085            expression: the SQL code string to parse.
1086                This can also be an integer.
1087                If a `Offset` instance is passed, this is used as-is.
1088                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1089            dialect: the dialect used to parse the input expression.
1090            copy: if `False`, modify this expression instance in-place.
1091            opts: other options to use to parse the input expressions.
1092
1093        Returns:
1094            The modified Select expression.
1095        """
1096        return _apply_builder(
1097            expression=expression,
1098            instance=self,
1099            arg="offset",
1100            into=Offset,
1101            prefix="OFFSET",
1102            dialect=dialect,
1103            copy=copy,
1104            into_arg="expression",
1105            **opts,
1106        )
1107
1108    def order_by(
1109        self: Q,
1110        *expressions: t.Optional[ExpOrStr],
1111        append: bool = True,
1112        dialect: DialectType = None,
1113        copy: bool = True,
1114        **opts,
1115    ) -> Q:
1116        """
1117        Set the ORDER BY expression.
1118
1119        Example:
1120            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1121            'SELECT x FROM tbl ORDER BY x DESC'
1122
1123        Args:
1124            *expressions: the SQL code strings to parse.
1125                If a `Group` instance is passed, this is used as-is.
1126                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1127            append: if `True`, add to any existing expressions.
1128                Otherwise, this flattens all the `Order` expression into a single expression.
1129            dialect: the dialect used to parse the input expression.
1130            copy: if `False`, modify this expression instance in-place.
1131            opts: other options to use to parse the input expressions.
1132
1133        Returns:
1134            The modified Select expression.
1135        """
1136        return _apply_child_list_builder(
1137            *expressions,
1138            instance=self,
1139            arg="order",
1140            append=append,
1141            copy=copy,
1142            prefix="ORDER BY",
1143            into=Order,
1144            dialect=dialect,
1145            **opts,
1146        )
1147
1148    @property
1149    def ctes(self) -> t.List[CTE]:
1150        """Returns a list of all the CTEs attached to this query."""
1151        with_ = self.args.get("with")
1152        return with_.expressions if with_ else []
1153
1154    @property
1155    def selects(self) -> t.List[Expression]:
1156        """Returns the query's projections."""
1157        raise NotImplementedError("Query objects must implement `selects`")
1158
1159    @property
1160    def named_selects(self) -> t.List[str]:
1161        """Returns the output names of the query's projections."""
1162        raise NotImplementedError("Query objects must implement `named_selects`")
1163
1164    def select(
1165        self: Q,
1166        *expressions: t.Optional[ExpOrStr],
1167        append: bool = True,
1168        dialect: DialectType = None,
1169        copy: bool = True,
1170        **opts,
1171    ) -> Q:
1172        """
1173        Append to or set the SELECT expressions.
1174
1175        Example:
1176            >>> Select().select("x", "y").sql()
1177            'SELECT x, y'
1178
1179        Args:
1180            *expressions: the SQL code strings to parse.
1181                If an `Expression` instance is passed, it will be used as-is.
1182            append: if `True`, add to any existing expressions.
1183                Otherwise, this resets the expressions.
1184            dialect: the dialect used to parse the input expressions.
1185            copy: if `False`, modify this expression instance in-place.
1186            opts: other options to use to parse the input expressions.
1187
1188        Returns:
1189            The modified Query expression.
1190        """
1191        raise NotImplementedError("Query objects must implement `select`")
1192
1193    def with_(
1194        self: Q,
1195        alias: ExpOrStr,
1196        as_: ExpOrStr,
1197        recursive: t.Optional[bool] = None,
1198        append: bool = True,
1199        dialect: DialectType = None,
1200        copy: bool = True,
1201        **opts,
1202    ) -> Q:
1203        """
1204        Append to or set the common table expressions.
1205
1206        Example:
1207            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1208            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1209
1210        Args:
1211            alias: the SQL code string to parse as the table name.
1212                If an `Expression` instance is passed, this is used as-is.
1213            as_: the SQL code string to parse as the table expression.
1214                If an `Expression` instance is passed, it will be used as-is.
1215            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1216            append: if `True`, add to any existing expressions.
1217                Otherwise, this resets the expressions.
1218            dialect: the dialect used to parse the input expression.
1219            copy: if `False`, modify this expression instance in-place.
1220            opts: other options to use to parse the input expressions.
1221
1222        Returns:
1223            The modified expression.
1224        """
1225        return _apply_cte_builder(
1226            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1227        )
1228
1229    def union(
1230        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1231    ) -> Union:
1232        """
1233        Builds a UNION expression.
1234
1235        Example:
1236            >>> import sqlglot
1237            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1238            'SELECT * FROM foo UNION SELECT * FROM bla'
1239
1240        Args:
1241            expression: the SQL code string.
1242                If an `Expression` instance is passed, it will be used as-is.
1243            distinct: set the DISTINCT flag if and only if this is true.
1244            dialect: the dialect used to parse the input expression.
1245            opts: other options to use to parse the input expressions.
1246
1247        Returns:
1248            The new Union expression.
1249        """
1250        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1251
1252    def intersect(
1253        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1254    ) -> Intersect:
1255        """
1256        Builds an INTERSECT expression.
1257
1258        Example:
1259            >>> import sqlglot
1260            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1261            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1262
1263        Args:
1264            expression: the SQL code string.
1265                If an `Expression` instance is passed, it will be used as-is.
1266            distinct: set the DISTINCT flag if and only if this is true.
1267            dialect: the dialect used to parse the input expression.
1268            opts: other options to use to parse the input expressions.
1269
1270        Returns:
1271            The new Intersect expression.
1272        """
1273        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1274
1275    def except_(
1276        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1277    ) -> Except:
1278        """
1279        Builds an EXCEPT expression.
1280
1281        Example:
1282            >>> import sqlglot
1283            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1284            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1285
1286        Args:
1287            expression: the SQL code string.
1288                If an `Expression` instance is passed, it will be used as-is.
1289            distinct: set the DISTINCT flag if and only if this is true.
1290            dialect: the dialect used to parse the input expression.
1291            opts: other options to use to parse the input expressions.
1292
1293        Returns:
1294            The new Except expression.
1295        """
1296        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1297
1298
1299class UDTF(DerivedTable):
1300    @property
1301    def selects(self) -> t.List[Expression]:
1302        alias = self.args.get("alias")
1303        return alias.columns if alias else []
1304
1305
1306class Cache(Expression):
1307    arg_types = {
1308        "this": True,
1309        "lazy": False,
1310        "options": False,
1311        "expression": False,
1312    }
1313
1314
1315class Uncache(Expression):
1316    arg_types = {"this": True, "exists": False}
1317
1318
1319class Refresh(Expression):
1320    pass
1321
1322
1323class DDL(Expression):
1324    @property
1325    def ctes(self) -> t.List[CTE]:
1326        """Returns a list of all the CTEs attached to this statement."""
1327        with_ = self.args.get("with")
1328        return with_.expressions if with_ else []
1329
1330    @property
1331    def selects(self) -> t.List[Expression]:
1332        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1333        return self.expression.selects if isinstance(self.expression, Query) else []
1334
1335    @property
1336    def named_selects(self) -> t.List[str]:
1337        """
1338        If this statement contains a query (e.g. a CTAS), this returns the output
1339        names of the query's projections.
1340        """
1341        return self.expression.named_selects if isinstance(self.expression, Query) else []
1342
1343
1344class DML(Expression):
1345    def returning(
1346        self,
1347        expression: ExpOrStr,
1348        dialect: DialectType = None,
1349        copy: bool = True,
1350        **opts,
1351    ) -> DML:
1352        """
1353        Set the RETURNING expression. Not supported by all dialects.
1354
1355        Example:
1356            >>> delete("tbl").returning("*", dialect="postgres").sql()
1357            'DELETE FROM tbl RETURNING *'
1358
1359        Args:
1360            expression: the SQL code strings to parse.
1361                If an `Expression` instance is passed, it will be used as-is.
1362            dialect: the dialect used to parse the input expressions.
1363            copy: if `False`, modify this expression instance in-place.
1364            opts: other options to use to parse the input expressions.
1365
1366        Returns:
1367            Delete: the modified expression.
1368        """
1369        return _apply_builder(
1370            expression=expression,
1371            instance=self,
1372            arg="returning",
1373            prefix="RETURNING",
1374            dialect=dialect,
1375            copy=copy,
1376            into=Returning,
1377            **opts,
1378        )
1379
1380
1381class Create(DDL):
1382    arg_types = {
1383        "with": False,
1384        "this": True,
1385        "kind": True,
1386        "expression": False,
1387        "exists": False,
1388        "properties": False,
1389        "replace": False,
1390        "unique": False,
1391        "indexes": False,
1392        "no_schema_binding": False,
1393        "begin": False,
1394        "end": False,
1395        "clone": False,
1396    }
1397
1398    @property
1399    def kind(self) -> t.Optional[str]:
1400        kind = self.args.get("kind")
1401        return kind and kind.upper()
1402
1403
1404class SequenceProperties(Expression):
1405    arg_types = {
1406        "increment": False,
1407        "minvalue": False,
1408        "maxvalue": False,
1409        "cache": False,
1410        "start": False,
1411        "owned": False,
1412        "options": False,
1413    }
1414
1415
1416class TruncateTable(Expression):
1417    arg_types = {
1418        "expressions": True,
1419        "is_database": False,
1420        "exists": False,
1421        "only": False,
1422        "cluster": False,
1423        "identity": False,
1424        "option": False,
1425        "partition": False,
1426    }
1427
1428
1429# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1430# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1431# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1432class Clone(Expression):
1433    arg_types = {"this": True, "shallow": False, "copy": False}
1434
1435
1436class Describe(Expression):
1437    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1438
1439
1440class Kill(Expression):
1441    arg_types = {"this": True, "kind": False}
1442
1443
1444class Pragma(Expression):
1445    pass
1446
1447
1448class Set(Expression):
1449    arg_types = {"expressions": False, "unset": False, "tag": False}
1450
1451
1452class Heredoc(Expression):
1453    arg_types = {"this": True, "tag": False}
1454
1455
1456class SetItem(Expression):
1457    arg_types = {
1458        "this": False,
1459        "expressions": False,
1460        "kind": False,
1461        "collate": False,  # MySQL SET NAMES statement
1462        "global": False,
1463    }
1464
1465
1466class Show(Expression):
1467    arg_types = {
1468        "this": True,
1469        "history": False,
1470        "terse": False,
1471        "target": False,
1472        "offset": False,
1473        "starts_with": False,
1474        "limit": False,
1475        "from": False,
1476        "like": False,
1477        "where": False,
1478        "db": False,
1479        "scope": False,
1480        "scope_kind": False,
1481        "full": False,
1482        "mutex": False,
1483        "query": False,
1484        "channel": False,
1485        "global": False,
1486        "log": False,
1487        "position": False,
1488        "types": False,
1489    }
1490
1491
1492class UserDefinedFunction(Expression):
1493    arg_types = {"this": True, "expressions": False, "wrapped": False}
1494
1495
1496class CharacterSet(Expression):
1497    arg_types = {"this": True, "default": False}
1498
1499
1500class With(Expression):
1501    arg_types = {"expressions": True, "recursive": False}
1502
1503    @property
1504    def recursive(self) -> bool:
1505        return bool(self.args.get("recursive"))
1506
1507
1508class WithinGroup(Expression):
1509    arg_types = {"this": True, "expression": False}
1510
1511
1512# clickhouse supports scalar ctes
1513# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1514class CTE(DerivedTable):
1515    arg_types = {
1516        "this": True,
1517        "alias": True,
1518        "scalar": False,
1519        "materialized": False,
1520    }
1521
1522
1523class TableAlias(Expression):
1524    arg_types = {"this": False, "columns": False}
1525
1526    @property
1527    def columns(self):
1528        return self.args.get("columns") or []
1529
1530
1531class BitString(Condition):
1532    pass
1533
1534
1535class HexString(Condition):
1536    pass
1537
1538
1539class ByteString(Condition):
1540    pass
1541
1542
1543class RawString(Condition):
1544    pass
1545
1546
1547class UnicodeString(Condition):
1548    arg_types = {"this": True, "escape": False}
1549
1550
1551class Column(Condition):
1552    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1553
1554    @property
1555    def table(self) -> str:
1556        return self.text("table")
1557
1558    @property
1559    def db(self) -> str:
1560        return self.text("db")
1561
1562    @property
1563    def catalog(self) -> str:
1564        return self.text("catalog")
1565
1566    @property
1567    def output_name(self) -> str:
1568        return self.name
1569
1570    @property
1571    def parts(self) -> t.List[Identifier]:
1572        """Return the parts of a column in order catalog, db, table, name."""
1573        return [
1574            t.cast(Identifier, self.args[part])
1575            for part in ("catalog", "db", "table", "this")
1576            if self.args.get(part)
1577        ]
1578
1579    def to_dot(self) -> Dot | Identifier:
1580        """Converts the column into a dot expression."""
1581        parts = self.parts
1582        parent = self.parent
1583
1584        while parent:
1585            if isinstance(parent, Dot):
1586                parts.append(parent.expression)
1587            parent = parent.parent
1588
1589        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1590
1591
1592class ColumnPosition(Expression):
1593    arg_types = {"this": False, "position": True}
1594
1595
1596class ColumnDef(Expression):
1597    arg_types = {
1598        "this": True,
1599        "kind": False,
1600        "constraints": False,
1601        "exists": False,
1602        "position": False,
1603    }
1604
1605    @property
1606    def constraints(self) -> t.List[ColumnConstraint]:
1607        return self.args.get("constraints") or []
1608
1609    @property
1610    def kind(self) -> t.Optional[DataType]:
1611        return self.args.get("kind")
1612
1613
1614class AlterColumn(Expression):
1615    arg_types = {
1616        "this": True,
1617        "dtype": False,
1618        "collate": False,
1619        "using": False,
1620        "default": False,
1621        "drop": False,
1622        "comment": False,
1623    }
1624
1625
1626# https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
1627class AlterDistStyle(Expression):
1628    pass
1629
1630
1631class AlterSortKey(Expression):
1632    arg_types = {"this": False, "expressions": False, "compound": False}
1633
1634
1635class RenameColumn(Expression):
1636    arg_types = {"this": True, "to": True, "exists": False}
1637
1638
1639class RenameTable(Expression):
1640    pass
1641
1642
1643class SwapTable(Expression):
1644    pass
1645
1646
1647class Comment(Expression):
1648    arg_types = {
1649        "this": True,
1650        "kind": True,
1651        "expression": True,
1652        "exists": False,
1653        "materialized": False,
1654    }
1655
1656
1657class Comprehension(Expression):
1658    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1659
1660
1661# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1662class MergeTreeTTLAction(Expression):
1663    arg_types = {
1664        "this": True,
1665        "delete": False,
1666        "recompress": False,
1667        "to_disk": False,
1668        "to_volume": False,
1669    }
1670
1671
1672# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1673class MergeTreeTTL(Expression):
1674    arg_types = {
1675        "expressions": True,
1676        "where": False,
1677        "group": False,
1678        "aggregates": False,
1679    }
1680
1681
1682# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1683class IndexConstraintOption(Expression):
1684    arg_types = {
1685        "key_block_size": False,
1686        "using": False,
1687        "parser": False,
1688        "comment": False,
1689        "visible": False,
1690        "engine_attr": False,
1691        "secondary_engine_attr": False,
1692    }
1693
1694
1695class ColumnConstraint(Expression):
1696    arg_types = {"this": False, "kind": True}
1697
1698    @property
1699    def kind(self) -> ColumnConstraintKind:
1700        return self.args["kind"]
1701
1702
1703class ColumnConstraintKind(Expression):
1704    pass
1705
1706
1707class AutoIncrementColumnConstraint(ColumnConstraintKind):
1708    pass
1709
1710
1711class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1712    arg_types = {"this": True, "expression": True}
1713
1714
1715class CaseSpecificColumnConstraint(ColumnConstraintKind):
1716    arg_types = {"not_": True}
1717
1718
1719class CharacterSetColumnConstraint(ColumnConstraintKind):
1720    arg_types = {"this": True}
1721
1722
1723class CheckColumnConstraint(ColumnConstraintKind):
1724    arg_types = {"this": True, "enforced": False}
1725
1726
1727class ClusteredColumnConstraint(ColumnConstraintKind):
1728    pass
1729
1730
1731class CollateColumnConstraint(ColumnConstraintKind):
1732    pass
1733
1734
1735class CommentColumnConstraint(ColumnConstraintKind):
1736    pass
1737
1738
1739class CompressColumnConstraint(ColumnConstraintKind):
1740    pass
1741
1742
1743class DateFormatColumnConstraint(ColumnConstraintKind):
1744    arg_types = {"this": True}
1745
1746
1747class DefaultColumnConstraint(ColumnConstraintKind):
1748    pass
1749
1750
1751class EncodeColumnConstraint(ColumnConstraintKind):
1752    pass
1753
1754
1755# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1756class ExcludeColumnConstraint(ColumnConstraintKind):
1757    pass
1758
1759
1760class EphemeralColumnConstraint(ColumnConstraintKind):
1761    arg_types = {"this": False}
1762
1763
1764class WithOperator(Expression):
1765    arg_types = {"this": True, "op": True}
1766
1767
1768class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1769    # this: True -> ALWAYS, this: False -> BY DEFAULT
1770    arg_types = {
1771        "this": False,
1772        "expression": False,
1773        "on_null": False,
1774        "start": False,
1775        "increment": False,
1776        "minvalue": False,
1777        "maxvalue": False,
1778        "cycle": False,
1779    }
1780
1781
1782class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1783    arg_types = {"start": False, "hidden": False}
1784
1785
1786# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1787# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1788class IndexColumnConstraint(ColumnConstraintKind):
1789    arg_types = {
1790        "this": False,
1791        "expressions": False,
1792        "kind": False,
1793        "index_type": False,
1794        "options": False,
1795        "expression": False,  # Clickhouse
1796        "granularity": False,
1797    }
1798
1799
1800class InlineLengthColumnConstraint(ColumnConstraintKind):
1801    pass
1802
1803
1804class NonClusteredColumnConstraint(ColumnConstraintKind):
1805    pass
1806
1807
1808class NotForReplicationColumnConstraint(ColumnConstraintKind):
1809    arg_types = {}
1810
1811
1812class NotNullColumnConstraint(ColumnConstraintKind):
1813    arg_types = {"allow_null": False}
1814
1815
1816# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1817class OnUpdateColumnConstraint(ColumnConstraintKind):
1818    pass
1819
1820
1821# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1822class TransformColumnConstraint(ColumnConstraintKind):
1823    pass
1824
1825
1826class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1827    arg_types = {"desc": False}
1828
1829
1830class TitleColumnConstraint(ColumnConstraintKind):
1831    pass
1832
1833
1834class UniqueColumnConstraint(ColumnConstraintKind):
1835    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1836
1837
1838class UppercaseColumnConstraint(ColumnConstraintKind):
1839    arg_types: t.Dict[str, t.Any] = {}
1840
1841
1842class PathColumnConstraint(ColumnConstraintKind):
1843    pass
1844
1845
1846# computed column expression
1847# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1848class ComputedColumnConstraint(ColumnConstraintKind):
1849    arg_types = {"this": True, "persisted": False, "not_null": False}
1850
1851
1852class Constraint(Expression):
1853    arg_types = {"this": True, "expressions": True}
1854
1855
1856class Delete(DML):
1857    arg_types = {
1858        "with": False,
1859        "this": False,
1860        "using": False,
1861        "where": False,
1862        "returning": False,
1863        "limit": False,
1864        "tables": False,  # Multiple-Table Syntax (MySQL)
1865    }
1866
1867    def delete(
1868        self,
1869        table: ExpOrStr,
1870        dialect: DialectType = None,
1871        copy: bool = True,
1872        **opts,
1873    ) -> Delete:
1874        """
1875        Create a DELETE expression or replace the table on an existing DELETE expression.
1876
1877        Example:
1878            >>> delete("tbl").sql()
1879            'DELETE FROM tbl'
1880
1881        Args:
1882            table: the table from which to delete.
1883            dialect: the dialect used to parse the input expression.
1884            copy: if `False`, modify this expression instance in-place.
1885            opts: other options to use to parse the input expressions.
1886
1887        Returns:
1888            Delete: the modified expression.
1889        """
1890        return _apply_builder(
1891            expression=table,
1892            instance=self,
1893            arg="this",
1894            dialect=dialect,
1895            into=Table,
1896            copy=copy,
1897            **opts,
1898        )
1899
1900    def where(
1901        self,
1902        *expressions: t.Optional[ExpOrStr],
1903        append: bool = True,
1904        dialect: DialectType = None,
1905        copy: bool = True,
1906        **opts,
1907    ) -> Delete:
1908        """
1909        Append to or set the WHERE expressions.
1910
1911        Example:
1912            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1913            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1914
1915        Args:
1916            *expressions: the SQL code strings to parse.
1917                If an `Expression` instance is passed, it will be used as-is.
1918                Multiple expressions are combined with an AND operator.
1919            append: if `True`, AND the new expressions to any existing expression.
1920                Otherwise, this resets the expression.
1921            dialect: the dialect used to parse the input expressions.
1922            copy: if `False`, modify this expression instance in-place.
1923            opts: other options to use to parse the input expressions.
1924
1925        Returns:
1926            Delete: the modified expression.
1927        """
1928        return _apply_conjunction_builder(
1929            *expressions,
1930            instance=self,
1931            arg="where",
1932            append=append,
1933            into=Where,
1934            dialect=dialect,
1935            copy=copy,
1936            **opts,
1937        )
1938
1939
1940class Drop(Expression):
1941    arg_types = {
1942        "this": False,
1943        "kind": False,
1944        "expressions": False,
1945        "exists": False,
1946        "temporary": False,
1947        "materialized": False,
1948        "cascade": False,
1949        "constraints": False,
1950        "purge": False,
1951        "cluster": False,
1952    }
1953
1954
1955class Filter(Expression):
1956    arg_types = {"this": True, "expression": True}
1957
1958
1959class Check(Expression):
1960    pass
1961
1962
1963# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1964class Connect(Expression):
1965    arg_types = {"start": False, "connect": True, "nocycle": False}
1966
1967
1968class CopyParameter(Expression):
1969    arg_types = {"this": True, "expression": False}
1970
1971
1972class Copy(Expression):
1973    arg_types = {
1974        "this": True,
1975        "kind": True,
1976        "files": True,
1977        "credentials": False,
1978        "format": False,
1979        "params": False,
1980    }
1981
1982
1983class Credentials(Expression):
1984    arg_types = {
1985        "credentials": False,
1986        "encryption": False,
1987        "storage": False,
1988        "iam_role": False,
1989        "region": False,
1990    }
1991
1992
1993class Prior(Expression):
1994    pass
1995
1996
1997class Directory(Expression):
1998    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1999    arg_types = {"this": True, "local": False, "row_format": False}
2000
2001
2002class ForeignKey(Expression):
2003    arg_types = {
2004        "expressions": True,
2005        "reference": False,
2006        "delete": False,
2007        "update": False,
2008    }
2009
2010
2011class ColumnPrefix(Expression):
2012    arg_types = {"this": True, "expression": True}
2013
2014
2015class PrimaryKey(Expression):
2016    arg_types = {"expressions": True, "options": False}
2017
2018
2019# https://www.postgresql.org/docs/9.1/sql-selectinto.html
2020# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
2021class Into(Expression):
2022    arg_types = {"this": True, "temporary": False, "unlogged": False}
2023
2024
2025class From(Expression):
2026    @property
2027    def name(self) -> str:
2028        return self.this.name
2029
2030    @property
2031    def alias_or_name(self) -> str:
2032        return self.this.alias_or_name
2033
2034
2035class Having(Expression):
2036    pass
2037
2038
2039class Hint(Expression):
2040    arg_types = {"expressions": True}
2041
2042
2043class JoinHint(Expression):
2044    arg_types = {"this": True, "expressions": True}
2045
2046
2047class Identifier(Expression):
2048    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2049
2050    @property
2051    def quoted(self) -> bool:
2052        return bool(self.args.get("quoted"))
2053
2054    @property
2055    def hashable_args(self) -> t.Any:
2056        return (self.this, self.quoted)
2057
2058    @property
2059    def output_name(self) -> str:
2060        return self.name
2061
2062
2063# https://www.postgresql.org/docs/current/indexes-opclass.html
2064class Opclass(Expression):
2065    arg_types = {"this": True, "expression": True}
2066
2067
2068class Index(Expression):
2069    arg_types = {
2070        "this": False,
2071        "table": False,
2072        "unique": False,
2073        "primary": False,
2074        "amp": False,  # teradata
2075        "params": False,
2076    }
2077
2078
2079class IndexParameters(Expression):
2080    arg_types = {
2081        "using": False,
2082        "include": False,
2083        "columns": False,
2084        "with_storage": False,
2085        "partition_by": False,
2086        "tablespace": False,
2087        "where": False,
2088    }
2089
2090
2091class Insert(DDL, DML):
2092    arg_types = {
2093        "hint": False,
2094        "with": False,
2095        "is_function": False,
2096        "this": False,
2097        "expression": False,
2098        "conflict": False,
2099        "returning": False,
2100        "overwrite": False,
2101        "exists": False,
2102        "alternative": False,
2103        "where": False,
2104        "ignore": False,
2105        "by_name": False,
2106        "stored": False,
2107    }
2108
2109    def with_(
2110        self,
2111        alias: ExpOrStr,
2112        as_: ExpOrStr,
2113        recursive: t.Optional[bool] = None,
2114        append: bool = True,
2115        dialect: DialectType = None,
2116        copy: bool = True,
2117        **opts,
2118    ) -> Insert:
2119        """
2120        Append to or set the common table expressions.
2121
2122        Example:
2123            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2124            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2125
2126        Args:
2127            alias: the SQL code string to parse as the table name.
2128                If an `Expression` instance is passed, this is used as-is.
2129            as_: the SQL code string to parse as the table expression.
2130                If an `Expression` instance is passed, it will be used as-is.
2131            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2132            append: if `True`, add to any existing expressions.
2133                Otherwise, this resets the expressions.
2134            dialect: the dialect used to parse the input expression.
2135            copy: if `False`, modify this expression instance in-place.
2136            opts: other options to use to parse the input expressions.
2137
2138        Returns:
2139            The modified expression.
2140        """
2141        return _apply_cte_builder(
2142            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2143        )
2144
2145
2146class OnConflict(Expression):
2147    arg_types = {
2148        "duplicate": False,
2149        "expressions": False,
2150        "action": False,
2151        "conflict_keys": False,
2152        "constraint": False,
2153    }
2154
2155
2156class Returning(Expression):
2157    arg_types = {"expressions": True, "into": False}
2158
2159
2160# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2161class Introducer(Expression):
2162    arg_types = {"this": True, "expression": True}
2163
2164
2165# national char, like n'utf8'
2166class National(Expression):
2167    pass
2168
2169
2170class LoadData(Expression):
2171    arg_types = {
2172        "this": True,
2173        "local": False,
2174        "overwrite": False,
2175        "inpath": True,
2176        "partition": False,
2177        "input_format": False,
2178        "serde": False,
2179    }
2180
2181
2182class Partition(Expression):
2183    arg_types = {"expressions": True}
2184
2185
2186class PartitionRange(Expression):
2187    arg_types = {"this": True, "expression": True}
2188
2189
2190# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
2191class PartitionId(Expression):
2192    pass
2193
2194
2195class Fetch(Expression):
2196    arg_types = {
2197        "direction": False,
2198        "count": False,
2199        "percent": False,
2200        "with_ties": False,
2201    }
2202
2203
2204class Group(Expression):
2205    arg_types = {
2206        "expressions": False,
2207        "grouping_sets": False,
2208        "cube": False,
2209        "rollup": False,
2210        "totals": False,
2211        "all": False,
2212    }
2213
2214
2215class Lambda(Expression):
2216    arg_types = {"this": True, "expressions": True}
2217
2218
2219class Limit(Expression):
2220    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2221
2222
2223class Literal(Condition):
2224    arg_types = {"this": True, "is_string": True}
2225
2226    @property
2227    def hashable_args(self) -> t.Any:
2228        return (self.this, self.args.get("is_string"))
2229
2230    @classmethod
2231    def number(cls, number) -> Literal:
2232        return cls(this=str(number), is_string=False)
2233
2234    @classmethod
2235    def string(cls, string) -> Literal:
2236        return cls(this=str(string), is_string=True)
2237
2238    @property
2239    def output_name(self) -> str:
2240        return self.name
2241
2242
2243class Join(Expression):
2244    arg_types = {
2245        "this": True,
2246        "on": False,
2247        "side": False,
2248        "kind": False,
2249        "using": False,
2250        "method": False,
2251        "global": False,
2252        "hint": False,
2253        "match_condition": False,  # Snowflake
2254    }
2255
2256    @property
2257    def method(self) -> str:
2258        return self.text("method").upper()
2259
2260    @property
2261    def kind(self) -> str:
2262        return self.text("kind").upper()
2263
2264    @property
2265    def side(self) -> str:
2266        return self.text("side").upper()
2267
2268    @property
2269    def hint(self) -> str:
2270        return self.text("hint").upper()
2271
2272    @property
2273    def alias_or_name(self) -> str:
2274        return self.this.alias_or_name
2275
2276    def on(
2277        self,
2278        *expressions: t.Optional[ExpOrStr],
2279        append: bool = True,
2280        dialect: DialectType = None,
2281        copy: bool = True,
2282        **opts,
2283    ) -> Join:
2284        """
2285        Append to or set the ON expressions.
2286
2287        Example:
2288            >>> import sqlglot
2289            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2290            'JOIN x ON y = 1'
2291
2292        Args:
2293            *expressions: the SQL code strings to parse.
2294                If an `Expression` instance is passed, it will be used as-is.
2295                Multiple expressions are combined with an AND operator.
2296            append: if `True`, AND the new expressions to any existing expression.
2297                Otherwise, this resets the expression.
2298            dialect: the dialect used to parse the input expressions.
2299            copy: if `False`, modify this expression instance in-place.
2300            opts: other options to use to parse the input expressions.
2301
2302        Returns:
2303            The modified Join expression.
2304        """
2305        join = _apply_conjunction_builder(
2306            *expressions,
2307            instance=self,
2308            arg="on",
2309            append=append,
2310            dialect=dialect,
2311            copy=copy,
2312            **opts,
2313        )
2314
2315        if join.kind == "CROSS":
2316            join.set("kind", None)
2317
2318        return join
2319
2320    def using(
2321        self,
2322        *expressions: t.Optional[ExpOrStr],
2323        append: bool = True,
2324        dialect: DialectType = None,
2325        copy: bool = True,
2326        **opts,
2327    ) -> Join:
2328        """
2329        Append to or set the USING expressions.
2330
2331        Example:
2332            >>> import sqlglot
2333            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2334            'JOIN x USING (foo, bla)'
2335
2336        Args:
2337            *expressions: the SQL code strings to parse.
2338                If an `Expression` instance is passed, it will be used as-is.
2339            append: if `True`, concatenate the new expressions to the existing "using" list.
2340                Otherwise, this resets the expression.
2341            dialect: the dialect used to parse the input expressions.
2342            copy: if `False`, modify this expression instance in-place.
2343            opts: other options to use to parse the input expressions.
2344
2345        Returns:
2346            The modified Join expression.
2347        """
2348        join = _apply_list_builder(
2349            *expressions,
2350            instance=self,
2351            arg="using",
2352            append=append,
2353            dialect=dialect,
2354            copy=copy,
2355            **opts,
2356        )
2357
2358        if join.kind == "CROSS":
2359            join.set("kind", None)
2360
2361        return join
2362
2363
2364class Lateral(UDTF):
2365    arg_types = {
2366        "this": True,
2367        "view": False,
2368        "outer": False,
2369        "alias": False,
2370        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2371    }
2372
2373
2374class MatchRecognizeMeasure(Expression):
2375    arg_types = {
2376        "this": True,
2377        "window_frame": False,
2378    }
2379
2380
2381class MatchRecognize(Expression):
2382    arg_types = {
2383        "partition_by": False,
2384        "order": False,
2385        "measures": False,
2386        "rows": False,
2387        "after": False,
2388        "pattern": False,
2389        "define": False,
2390        "alias": False,
2391    }
2392
2393
2394# Clickhouse FROM FINAL modifier
2395# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2396class Final(Expression):
2397    pass
2398
2399
2400class Offset(Expression):
2401    arg_types = {"this": False, "expression": True, "expressions": False}
2402
2403
2404class Order(Expression):
2405    arg_types = {
2406        "this": False,
2407        "expressions": True,
2408        "interpolate": False,
2409        "siblings": False,
2410    }
2411
2412
2413# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2414class WithFill(Expression):
2415    arg_types = {"from": False, "to": False, "step": False}
2416
2417
2418# hive specific sorts
2419# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2420class Cluster(Order):
2421    pass
2422
2423
2424class Distribute(Order):
2425    pass
2426
2427
2428class Sort(Order):
2429    pass
2430
2431
2432class Ordered(Expression):
2433    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2434
2435
2436class Property(Expression):
2437    arg_types = {"this": True, "value": True}
2438
2439
2440class AlgorithmProperty(Property):
2441    arg_types = {"this": True}
2442
2443
2444class AutoIncrementProperty(Property):
2445    arg_types = {"this": True}
2446
2447
2448# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2449class AutoRefreshProperty(Property):
2450    arg_types = {"this": True}
2451
2452
2453class BackupProperty(Property):
2454    arg_types = {"this": True}
2455
2456
2457class BlockCompressionProperty(Property):
2458    arg_types = {
2459        "autotemp": False,
2460        "always": False,
2461        "default": False,
2462        "manual": False,
2463        "never": False,
2464    }
2465
2466
2467class CharacterSetProperty(Property):
2468    arg_types = {"this": True, "default": True}
2469
2470
2471class ChecksumProperty(Property):
2472    arg_types = {"on": False, "default": False}
2473
2474
2475class CollateProperty(Property):
2476    arg_types = {"this": True, "default": False}
2477
2478
2479class CopyGrantsProperty(Property):
2480    arg_types = {}
2481
2482
2483class DataBlocksizeProperty(Property):
2484    arg_types = {
2485        "size": False,
2486        "units": False,
2487        "minimum": False,
2488        "maximum": False,
2489        "default": False,
2490    }
2491
2492
2493class DefinerProperty(Property):
2494    arg_types = {"this": True}
2495
2496
2497class DistKeyProperty(Property):
2498    arg_types = {"this": True}
2499
2500
2501class DistStyleProperty(Property):
2502    arg_types = {"this": True}
2503
2504
2505class EngineProperty(Property):
2506    arg_types = {"this": True}
2507
2508
2509class HeapProperty(Property):
2510    arg_types = {}
2511
2512
2513class ToTableProperty(Property):
2514    arg_types = {"this": True}
2515
2516
2517class ExecuteAsProperty(Property):
2518    arg_types = {"this": True}
2519
2520
2521class ExternalProperty(Property):
2522    arg_types = {"this": False}
2523
2524
2525class FallbackProperty(Property):
2526    arg_types = {"no": True, "protection": False}
2527
2528
2529class FileFormatProperty(Property):
2530    arg_types = {"this": True}
2531
2532
2533class FreespaceProperty(Property):
2534    arg_types = {"this": True, "percent": False}
2535
2536
2537class GlobalProperty(Property):
2538    arg_types = {}
2539
2540
2541class IcebergProperty(Property):
2542    arg_types = {}
2543
2544
2545class InheritsProperty(Property):
2546    arg_types = {"expressions": True}
2547
2548
2549class InputModelProperty(Property):
2550    arg_types = {"this": True}
2551
2552
2553class OutputModelProperty(Property):
2554    arg_types = {"this": True}
2555
2556
2557class IsolatedLoadingProperty(Property):
2558    arg_types = {"no": False, "concurrent": False, "target": False}
2559
2560
2561class JournalProperty(Property):
2562    arg_types = {
2563        "no": False,
2564        "dual": False,
2565        "before": False,
2566        "local": False,
2567        "after": False,
2568    }
2569
2570
2571class LanguageProperty(Property):
2572    arg_types = {"this": True}
2573
2574
2575# spark ddl
2576class ClusteredByProperty(Property):
2577    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2578
2579
2580class DictProperty(Property):
2581    arg_types = {"this": True, "kind": True, "settings": False}
2582
2583
2584class DictSubProperty(Property):
2585    pass
2586
2587
2588class DictRange(Property):
2589    arg_types = {"this": True, "min": True, "max": True}
2590
2591
2592# Clickhouse CREATE ... ON CLUSTER modifier
2593# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2594class OnCluster(Property):
2595    arg_types = {"this": True}
2596
2597
2598class LikeProperty(Property):
2599    arg_types = {"this": True, "expressions": False}
2600
2601
2602class LocationProperty(Property):
2603    arg_types = {"this": True}
2604
2605
2606class LockProperty(Property):
2607    arg_types = {"this": True}
2608
2609
2610class LockingProperty(Property):
2611    arg_types = {
2612        "this": False,
2613        "kind": True,
2614        "for_or_in": False,
2615        "lock_type": True,
2616        "override": False,
2617    }
2618
2619
2620class LogProperty(Property):
2621    arg_types = {"no": True}
2622
2623
2624class MaterializedProperty(Property):
2625    arg_types = {"this": False}
2626
2627
2628class MergeBlockRatioProperty(Property):
2629    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2630
2631
2632class NoPrimaryIndexProperty(Property):
2633    arg_types = {}
2634
2635
2636class OnProperty(Property):
2637    arg_types = {"this": True}
2638
2639
2640class OnCommitProperty(Property):
2641    arg_types = {"delete": False}
2642
2643
2644class PartitionedByProperty(Property):
2645    arg_types = {"this": True}
2646
2647
2648# https://www.postgresql.org/docs/current/sql-createtable.html
2649class PartitionBoundSpec(Expression):
2650    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2651    arg_types = {
2652        "this": False,
2653        "expression": False,
2654        "from_expressions": False,
2655        "to_expressions": False,
2656    }
2657
2658
2659class PartitionedOfProperty(Property):
2660    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2661    arg_types = {"this": True, "expression": True}
2662
2663
2664class RemoteWithConnectionModelProperty(Property):
2665    arg_types = {"this": True}
2666
2667
2668class ReturnsProperty(Property):
2669    arg_types = {"this": True, "is_table": False, "table": False}
2670
2671
2672class RowFormatProperty(Property):
2673    arg_types = {"this": True}
2674
2675
2676class RowFormatDelimitedProperty(Property):
2677    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2678    arg_types = {
2679        "fields": False,
2680        "escaped": False,
2681        "collection_items": False,
2682        "map_keys": False,
2683        "lines": False,
2684        "null": False,
2685        "serde": False,
2686    }
2687
2688
2689class RowFormatSerdeProperty(Property):
2690    arg_types = {"this": True, "serde_properties": False}
2691
2692
2693# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2694class QueryTransform(Expression):
2695    arg_types = {
2696        "expressions": True,
2697        "command_script": True,
2698        "schema": False,
2699        "row_format_before": False,
2700        "record_writer": False,
2701        "row_format_after": False,
2702        "record_reader": False,
2703    }
2704
2705
2706class SampleProperty(Property):
2707    arg_types = {"this": True}
2708
2709
2710class SchemaCommentProperty(Property):
2711    arg_types = {"this": True}
2712
2713
2714class SerdeProperties(Property):
2715    arg_types = {"expressions": True}
2716
2717
2718class SetProperty(Property):
2719    arg_types = {"multi": True}
2720
2721
2722class SharingProperty(Property):
2723    arg_types = {"this": False}
2724
2725
2726class SetConfigProperty(Property):
2727    arg_types = {"this": True}
2728
2729
2730class SettingsProperty(Property):
2731    arg_types = {"expressions": True}
2732
2733
2734class SortKeyProperty(Property):
2735    arg_types = {"this": True, "compound": False}
2736
2737
2738class SqlReadWriteProperty(Property):
2739    arg_types = {"this": True}
2740
2741
2742class SqlSecurityProperty(Property):
2743    arg_types = {"definer": True}
2744
2745
2746class StabilityProperty(Property):
2747    arg_types = {"this": True}
2748
2749
2750class TemporaryProperty(Property):
2751    arg_types = {"this": False}
2752
2753
2754class TransformModelProperty(Property):
2755    arg_types = {"expressions": True}
2756
2757
2758class TransientProperty(Property):
2759    arg_types = {"this": False}
2760
2761
2762class UnloggedProperty(Property):
2763    arg_types = {}
2764
2765
2766# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2767class ViewAttributeProperty(Property):
2768    arg_types = {"this": True}
2769
2770
2771class VolatileProperty(Property):
2772    arg_types = {"this": False}
2773
2774
2775class WithDataProperty(Property):
2776    arg_types = {"no": True, "statistics": False}
2777
2778
2779class WithJournalTableProperty(Property):
2780    arg_types = {"this": True}
2781
2782
2783class WithSystemVersioningProperty(Property):
2784    # this -> history table name, expression -> data consistency check
2785    arg_types = {"this": False, "expression": False}
2786
2787
2788class Properties(Expression):
2789    arg_types = {"expressions": True}
2790
2791    NAME_TO_PROPERTY = {
2792        "ALGORITHM": AlgorithmProperty,
2793        "AUTO_INCREMENT": AutoIncrementProperty,
2794        "CHARACTER SET": CharacterSetProperty,
2795        "CLUSTERED_BY": ClusteredByProperty,
2796        "COLLATE": CollateProperty,
2797        "COMMENT": SchemaCommentProperty,
2798        "DEFINER": DefinerProperty,
2799        "DISTKEY": DistKeyProperty,
2800        "DISTSTYLE": DistStyleProperty,
2801        "ENGINE": EngineProperty,
2802        "EXECUTE AS": ExecuteAsProperty,
2803        "FORMAT": FileFormatProperty,
2804        "LANGUAGE": LanguageProperty,
2805        "LOCATION": LocationProperty,
2806        "LOCK": LockProperty,
2807        "PARTITIONED_BY": PartitionedByProperty,
2808        "RETURNS": ReturnsProperty,
2809        "ROW_FORMAT": RowFormatProperty,
2810        "SORTKEY": SortKeyProperty,
2811    }
2812
2813    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2814
2815    # CREATE property locations
2816    # Form: schema specified
2817    #   create [POST_CREATE]
2818    #     table a [POST_NAME]
2819    #     (b int) [POST_SCHEMA]
2820    #     with ([POST_WITH])
2821    #     index (b) [POST_INDEX]
2822    #
2823    # Form: alias selection
2824    #   create [POST_CREATE]
2825    #     table a [POST_NAME]
2826    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2827    #     index (c) [POST_INDEX]
2828    class Location(AutoName):
2829        POST_CREATE = auto()
2830        POST_NAME = auto()
2831        POST_SCHEMA = auto()
2832        POST_WITH = auto()
2833        POST_ALIAS = auto()
2834        POST_EXPRESSION = auto()
2835        POST_INDEX = auto()
2836        UNSUPPORTED = auto()
2837
2838    @classmethod
2839    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2840        expressions = []
2841        for key, value in properties_dict.items():
2842            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2843            if property_cls:
2844                expressions.append(property_cls(this=convert(value)))
2845            else:
2846                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2847
2848        return cls(expressions=expressions)
2849
2850
2851class Qualify(Expression):
2852    pass
2853
2854
2855class InputOutputFormat(Expression):
2856    arg_types = {"input_format": False, "output_format": False}
2857
2858
2859# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2860class Return(Expression):
2861    pass
2862
2863
2864class Reference(Expression):
2865    arg_types = {"this": True, "expressions": False, "options": False}
2866
2867
2868class Tuple(Expression):
2869    arg_types = {"expressions": False}
2870
2871    def isin(
2872        self,
2873        *expressions: t.Any,
2874        query: t.Optional[ExpOrStr] = None,
2875        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2876        copy: bool = True,
2877        **opts,
2878    ) -> In:
2879        return In(
2880            this=maybe_copy(self, copy),
2881            expressions=[convert(e, copy=copy) for e in expressions],
2882            query=maybe_parse(query, copy=copy, **opts) if query else None,
2883            unnest=(
2884                Unnest(
2885                    expressions=[
2886                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2887                        for e in ensure_list(unnest)
2888                    ]
2889                )
2890                if unnest
2891                else None
2892            ),
2893        )
2894
2895
2896QUERY_MODIFIERS = {
2897    "match": False,
2898    "laterals": False,
2899    "joins": False,
2900    "connect": False,
2901    "pivots": False,
2902    "prewhere": False,
2903    "where": False,
2904    "group": False,
2905    "having": False,
2906    "qualify": False,
2907    "windows": False,
2908    "distribute": False,
2909    "sort": False,
2910    "cluster": False,
2911    "order": False,
2912    "limit": False,
2913    "offset": False,
2914    "locks": False,
2915    "sample": False,
2916    "settings": False,
2917    "format": False,
2918    "options": False,
2919}
2920
2921
2922# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2923# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2924class QueryOption(Expression):
2925    arg_types = {"this": True, "expression": False}
2926
2927
2928# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2929class WithTableHint(Expression):
2930    arg_types = {"expressions": True}
2931
2932
2933# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2934class IndexTableHint(Expression):
2935    arg_types = {"this": True, "expressions": False, "target": False}
2936
2937
2938# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2939class HistoricalData(Expression):
2940    arg_types = {"this": True, "kind": True, "expression": True}
2941
2942
2943class Table(Expression):
2944    arg_types = {
2945        "this": False,
2946        "alias": False,
2947        "db": False,
2948        "catalog": False,
2949        "laterals": False,
2950        "joins": False,
2951        "pivots": False,
2952        "hints": False,
2953        "system_time": False,
2954        "version": False,
2955        "format": False,
2956        "pattern": False,
2957        "ordinality": False,
2958        "when": False,
2959        "only": False,
2960        "partition": False,
2961    }
2962
2963    @property
2964    def name(self) -> str:
2965        if isinstance(self.this, Func):
2966            return ""
2967        return self.this.name
2968
2969    @property
2970    def db(self) -> str:
2971        return self.text("db")
2972
2973    @property
2974    def catalog(self) -> str:
2975        return self.text("catalog")
2976
2977    @property
2978    def selects(self) -> t.List[Expression]:
2979        return []
2980
2981    @property
2982    def named_selects(self) -> t.List[str]:
2983        return []
2984
2985    @property
2986    def parts(self) -> t.List[Expression]:
2987        """Return the parts of a table in order catalog, db, table."""
2988        parts: t.List[Expression] = []
2989
2990        for arg in ("catalog", "db", "this"):
2991            part = self.args.get(arg)
2992
2993            if isinstance(part, Dot):
2994                parts.extend(part.flatten())
2995            elif isinstance(part, Expression):
2996                parts.append(part)
2997
2998        return parts
2999
3000    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
3001        parts = self.parts
3002        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
3003        alias = self.args.get("alias")
3004        if alias:
3005            col = alias_(col, alias.this, copy=copy)
3006        return col
3007
3008
3009class Union(Query):
3010    arg_types = {
3011        "with": False,
3012        "this": True,
3013        "expression": True,
3014        "distinct": False,
3015        "by_name": False,
3016        **QUERY_MODIFIERS,
3017    }
3018
3019    def select(
3020        self,
3021        *expressions: t.Optional[ExpOrStr],
3022        append: bool = True,
3023        dialect: DialectType = None,
3024        copy: bool = True,
3025        **opts,
3026    ) -> Union:
3027        this = maybe_copy(self, copy)
3028        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3029        this.expression.unnest().select(
3030            *expressions, append=append, dialect=dialect, copy=False, **opts
3031        )
3032        return this
3033
3034    @property
3035    def named_selects(self) -> t.List[str]:
3036        return self.this.unnest().named_selects
3037
3038    @property
3039    def is_star(self) -> bool:
3040        return self.this.is_star or self.expression.is_star
3041
3042    @property
3043    def selects(self) -> t.List[Expression]:
3044        return self.this.unnest().selects
3045
3046    @property
3047    def left(self) -> Expression:
3048        return self.this
3049
3050    @property
3051    def right(self) -> Expression:
3052        return self.expression
3053
3054
3055class Except(Union):
3056    pass
3057
3058
3059class Intersect(Union):
3060    pass
3061
3062
3063class Unnest(UDTF):
3064    arg_types = {
3065        "expressions": True,
3066        "alias": False,
3067        "offset": False,
3068    }
3069
3070    @property
3071    def selects(self) -> t.List[Expression]:
3072        columns = super().selects
3073        offset = self.args.get("offset")
3074        if offset:
3075            columns = columns + [to_identifier("offset") if offset is True else offset]
3076        return columns
3077
3078
3079class Update(Expression):
3080    arg_types = {
3081        "with": False,
3082        "this": False,
3083        "expressions": True,
3084        "from": False,
3085        "where": False,
3086        "returning": False,
3087        "order": False,
3088        "limit": False,
3089    }
3090
3091
3092class Values(UDTF):
3093    arg_types = {"expressions": True, "alias": False}
3094
3095
3096class Var(Expression):
3097    pass
3098
3099
3100class Version(Expression):
3101    """
3102    Time travel, iceberg, bigquery etc
3103    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3104    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3105    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3106    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3107    this is either TIMESTAMP or VERSION
3108    kind is ("AS OF", "BETWEEN")
3109    """
3110
3111    arg_types = {"this": True, "kind": True, "expression": False}
3112
3113
3114class Schema(Expression):
3115    arg_types = {"this": False, "expressions": False}
3116
3117
3118# https://dev.mysql.com/doc/refman/8.0/en/select.html
3119# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3120class Lock(Expression):
3121    arg_types = {"update": True, "expressions": False, "wait": False}
3122
3123
3124class Select(Query):
3125    arg_types = {
3126        "with": False,
3127        "kind": False,
3128        "expressions": False,
3129        "hint": False,
3130        "distinct": False,
3131        "into": False,
3132        "from": False,
3133        **QUERY_MODIFIERS,
3134    }
3135
3136    def from_(
3137        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3138    ) -> Select:
3139        """
3140        Set the FROM expression.
3141
3142        Example:
3143            >>> Select().from_("tbl").select("x").sql()
3144            'SELECT x FROM tbl'
3145
3146        Args:
3147            expression : the SQL code strings to parse.
3148                If a `From` instance is passed, this is used as-is.
3149                If another `Expression` instance is passed, it will be wrapped in a `From`.
3150            dialect: the dialect used to parse the input expression.
3151            copy: if `False`, modify this expression instance in-place.
3152            opts: other options to use to parse the input expressions.
3153
3154        Returns:
3155            The modified Select expression.
3156        """
3157        return _apply_builder(
3158            expression=expression,
3159            instance=self,
3160            arg="from",
3161            into=From,
3162            prefix="FROM",
3163            dialect=dialect,
3164            copy=copy,
3165            **opts,
3166        )
3167
3168    def group_by(
3169        self,
3170        *expressions: t.Optional[ExpOrStr],
3171        append: bool = True,
3172        dialect: DialectType = None,
3173        copy: bool = True,
3174        **opts,
3175    ) -> Select:
3176        """
3177        Set the GROUP BY expression.
3178
3179        Example:
3180            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3181            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3182
3183        Args:
3184            *expressions: the SQL code strings to parse.
3185                If a `Group` instance is passed, this is used as-is.
3186                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3187                If nothing is passed in then a group by is not applied to the expression
3188            append: if `True`, add to any existing expressions.
3189                Otherwise, this flattens all the `Group` expression into a single expression.
3190            dialect: the dialect used to parse the input expression.
3191            copy: if `False`, modify this expression instance in-place.
3192            opts: other options to use to parse the input expressions.
3193
3194        Returns:
3195            The modified Select expression.
3196        """
3197        if not expressions:
3198            return self if not copy else self.copy()
3199
3200        return _apply_child_list_builder(
3201            *expressions,
3202            instance=self,
3203            arg="group",
3204            append=append,
3205            copy=copy,
3206            prefix="GROUP BY",
3207            into=Group,
3208            dialect=dialect,
3209            **opts,
3210        )
3211
3212    def sort_by(
3213        self,
3214        *expressions: t.Optional[ExpOrStr],
3215        append: bool = True,
3216        dialect: DialectType = None,
3217        copy: bool = True,
3218        **opts,
3219    ) -> Select:
3220        """
3221        Set the SORT BY expression.
3222
3223        Example:
3224            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3225            'SELECT x FROM tbl SORT BY x DESC'
3226
3227        Args:
3228            *expressions: the SQL code strings to parse.
3229                If a `Group` instance is passed, this is used as-is.
3230                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3231            append: if `True`, add to any existing expressions.
3232                Otherwise, this flattens all the `Order` expression into a single expression.
3233            dialect: the dialect used to parse the input expression.
3234            copy: if `False`, modify this expression instance in-place.
3235            opts: other options to use to parse the input expressions.
3236
3237        Returns:
3238            The modified Select expression.
3239        """
3240        return _apply_child_list_builder(
3241            *expressions,
3242            instance=self,
3243            arg="sort",
3244            append=append,
3245            copy=copy,
3246            prefix="SORT BY",
3247            into=Sort,
3248            dialect=dialect,
3249            **opts,
3250        )
3251
3252    def cluster_by(
3253        self,
3254        *expressions: t.Optional[ExpOrStr],
3255        append: bool = True,
3256        dialect: DialectType = None,
3257        copy: bool = True,
3258        **opts,
3259    ) -> Select:
3260        """
3261        Set the CLUSTER BY expression.
3262
3263        Example:
3264            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3265            'SELECT x FROM tbl CLUSTER BY x DESC'
3266
3267        Args:
3268            *expressions: the SQL code strings to parse.
3269                If a `Group` instance is passed, this is used as-is.
3270                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3271            append: if `True`, add to any existing expressions.
3272                Otherwise, this flattens all the `Order` expression into a single expression.
3273            dialect: the dialect used to parse the input expression.
3274            copy: if `False`, modify this expression instance in-place.
3275            opts: other options to use to parse the input expressions.
3276
3277        Returns:
3278            The modified Select expression.
3279        """
3280        return _apply_child_list_builder(
3281            *expressions,
3282            instance=self,
3283            arg="cluster",
3284            append=append,
3285            copy=copy,
3286            prefix="CLUSTER BY",
3287            into=Cluster,
3288            dialect=dialect,
3289            **opts,
3290        )
3291
3292    def select(
3293        self,
3294        *expressions: t.Optional[ExpOrStr],
3295        append: bool = True,
3296        dialect: DialectType = None,
3297        copy: bool = True,
3298        **opts,
3299    ) -> Select:
3300        return _apply_list_builder(
3301            *expressions,
3302            instance=self,
3303            arg="expressions",
3304            append=append,
3305            dialect=dialect,
3306            into=Expression,
3307            copy=copy,
3308            **opts,
3309        )
3310
3311    def lateral(
3312        self,
3313        *expressions: t.Optional[ExpOrStr],
3314        append: bool = True,
3315        dialect: DialectType = None,
3316        copy: bool = True,
3317        **opts,
3318    ) -> Select:
3319        """
3320        Append to or set the LATERAL expressions.
3321
3322        Example:
3323            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3324            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3325
3326        Args:
3327            *expressions: the SQL code strings to parse.
3328                If an `Expression` instance is passed, it will be used as-is.
3329            append: if `True`, add to any existing expressions.
3330                Otherwise, this resets the expressions.
3331            dialect: the dialect used to parse the input expressions.
3332            copy: if `False`, modify this expression instance in-place.
3333            opts: other options to use to parse the input expressions.
3334
3335        Returns:
3336            The modified Select expression.
3337        """
3338        return _apply_list_builder(
3339            *expressions,
3340            instance=self,
3341            arg="laterals",
3342            append=append,
3343            into=Lateral,
3344            prefix="LATERAL VIEW",
3345            dialect=dialect,
3346            copy=copy,
3347            **opts,
3348        )
3349
3350    def join(
3351        self,
3352        expression: ExpOrStr,
3353        on: t.Optional[ExpOrStr] = None,
3354        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3355        append: bool = True,
3356        join_type: t.Optional[str] = None,
3357        join_alias: t.Optional[Identifier | str] = None,
3358        dialect: DialectType = None,
3359        copy: bool = True,
3360        **opts,
3361    ) -> Select:
3362        """
3363        Append to or set the JOIN expressions.
3364
3365        Example:
3366            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3367            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3368
3369            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3370            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3371
3372            Use `join_type` to change the type of join:
3373
3374            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3375            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3376
3377        Args:
3378            expression: the SQL code string to parse.
3379                If an `Expression` instance is passed, it will be used as-is.
3380            on: optionally specify the join "on" criteria as a SQL string.
3381                If an `Expression` instance is passed, it will be used as-is.
3382            using: optionally specify the join "using" criteria as a SQL string.
3383                If an `Expression` instance is passed, it will be used as-is.
3384            append: if `True`, add to any existing expressions.
3385                Otherwise, this resets the expressions.
3386            join_type: if set, alter the parsed join type.
3387            join_alias: an optional alias for the joined source.
3388            dialect: the dialect used to parse the input expressions.
3389            copy: if `False`, modify this expression instance in-place.
3390            opts: other options to use to parse the input expressions.
3391
3392        Returns:
3393            Select: the modified expression.
3394        """
3395        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3396
3397        try:
3398            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3399        except ParseError:
3400            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3401
3402        join = expression if isinstance(expression, Join) else Join(this=expression)
3403
3404        if isinstance(join.this, Select):
3405            join.this.replace(join.this.subquery())
3406
3407        if join_type:
3408            method: t.Optional[Token]
3409            side: t.Optional[Token]
3410            kind: t.Optional[Token]
3411
3412            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3413
3414            if method:
3415                join.set("method", method.text)
3416            if side:
3417                join.set("side", side.text)
3418            if kind:
3419                join.set("kind", kind.text)
3420
3421        if on:
3422            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3423            join.set("on", on)
3424
3425        if using:
3426            join = _apply_list_builder(
3427                *ensure_list(using),
3428                instance=join,
3429                arg="using",
3430                append=append,
3431                copy=copy,
3432                into=Identifier,
3433                **opts,
3434            )
3435
3436        if join_alias:
3437            join.set("this", alias_(join.this, join_alias, table=True))
3438
3439        return _apply_list_builder(
3440            join,
3441            instance=self,
3442            arg="joins",
3443            append=append,
3444            copy=copy,
3445            **opts,
3446        )
3447
3448    def where(
3449        self,
3450        *expressions: t.Optional[ExpOrStr],
3451        append: bool = True,
3452        dialect: DialectType = None,
3453        copy: bool = True,
3454        **opts,
3455    ) -> Select:
3456        """
3457        Append to or set the WHERE expressions.
3458
3459        Example:
3460            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3461            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3462
3463        Args:
3464            *expressions: the SQL code strings to parse.
3465                If an `Expression` instance is passed, it will be used as-is.
3466                Multiple expressions are combined with an AND operator.
3467            append: if `True`, AND the new expressions to any existing expression.
3468                Otherwise, this resets the expression.
3469            dialect: the dialect used to parse the input expressions.
3470            copy: if `False`, modify this expression instance in-place.
3471            opts: other options to use to parse the input expressions.
3472
3473        Returns:
3474            Select: the modified expression.
3475        """
3476        return _apply_conjunction_builder(
3477            *expressions,
3478            instance=self,
3479            arg="where",
3480            append=append,
3481            into=Where,
3482            dialect=dialect,
3483            copy=copy,
3484            **opts,
3485        )
3486
3487    def having(
3488        self,
3489        *expressions: t.Optional[ExpOrStr],
3490        append: bool = True,
3491        dialect: DialectType = None,
3492        copy: bool = True,
3493        **opts,
3494    ) -> Select:
3495        """
3496        Append to or set the HAVING expressions.
3497
3498        Example:
3499            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3500            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3501
3502        Args:
3503            *expressions: the SQL code strings to parse.
3504                If an `Expression` instance is passed, it will be used as-is.
3505                Multiple expressions are combined with an AND operator.
3506            append: if `True`, AND the new expressions to any existing expression.
3507                Otherwise, this resets the expression.
3508            dialect: the dialect used to parse the input expressions.
3509            copy: if `False`, modify this expression instance in-place.
3510            opts: other options to use to parse the input expressions.
3511
3512        Returns:
3513            The modified Select expression.
3514        """
3515        return _apply_conjunction_builder(
3516            *expressions,
3517            instance=self,
3518            arg="having",
3519            append=append,
3520            into=Having,
3521            dialect=dialect,
3522            copy=copy,
3523            **opts,
3524        )
3525
3526    def window(
3527        self,
3528        *expressions: t.Optional[ExpOrStr],
3529        append: bool = True,
3530        dialect: DialectType = None,
3531        copy: bool = True,
3532        **opts,
3533    ) -> Select:
3534        return _apply_list_builder(
3535            *expressions,
3536            instance=self,
3537            arg="windows",
3538            append=append,
3539            into=Window,
3540            dialect=dialect,
3541            copy=copy,
3542            **opts,
3543        )
3544
3545    def qualify(
3546        self,
3547        *expressions: t.Optional[ExpOrStr],
3548        append: bool = True,
3549        dialect: DialectType = None,
3550        copy: bool = True,
3551        **opts,
3552    ) -> Select:
3553        return _apply_conjunction_builder(
3554            *expressions,
3555            instance=self,
3556            arg="qualify",
3557            append=append,
3558            into=Qualify,
3559            dialect=dialect,
3560            copy=copy,
3561            **opts,
3562        )
3563
3564    def distinct(
3565        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3566    ) -> Select:
3567        """
3568        Set the OFFSET expression.
3569
3570        Example:
3571            >>> Select().from_("tbl").select("x").distinct().sql()
3572            'SELECT DISTINCT x FROM tbl'
3573
3574        Args:
3575            ons: the expressions to distinct on
3576            distinct: whether the Select should be distinct
3577            copy: if `False`, modify this expression instance in-place.
3578
3579        Returns:
3580            Select: the modified expression.
3581        """
3582        instance = maybe_copy(self, copy)
3583        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3584        instance.set("distinct", Distinct(on=on) if distinct else None)
3585        return instance
3586
3587    def ctas(
3588        self,
3589        table: ExpOrStr,
3590        properties: t.Optional[t.Dict] = None,
3591        dialect: DialectType = None,
3592        copy: bool = True,
3593        **opts,
3594    ) -> Create:
3595        """
3596        Convert this expression to a CREATE TABLE AS statement.
3597
3598        Example:
3599            >>> Select().select("*").from_("tbl").ctas("x").sql()
3600            'CREATE TABLE x AS SELECT * FROM tbl'
3601
3602        Args:
3603            table: the SQL code string to parse as the table name.
3604                If another `Expression` instance is passed, it will be used as-is.
3605            properties: an optional mapping of table properties
3606            dialect: the dialect used to parse the input table.
3607            copy: if `False`, modify this expression instance in-place.
3608            opts: other options to use to parse the input table.
3609
3610        Returns:
3611            The new Create expression.
3612        """
3613        instance = maybe_copy(self, copy)
3614        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3615
3616        properties_expression = None
3617        if properties:
3618            properties_expression = Properties.from_dict(properties)
3619
3620        return Create(
3621            this=table_expression,
3622            kind="TABLE",
3623            expression=instance,
3624            properties=properties_expression,
3625        )
3626
3627    def lock(self, update: bool = True, copy: bool = True) -> Select:
3628        """
3629        Set the locking read mode for this expression.
3630
3631        Examples:
3632            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3633            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3634
3635            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3636            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3637
3638        Args:
3639            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3640            copy: if `False`, modify this expression instance in-place.
3641
3642        Returns:
3643            The modified expression.
3644        """
3645        inst = maybe_copy(self, copy)
3646        inst.set("locks", [Lock(update=update)])
3647
3648        return inst
3649
3650    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3651        """
3652        Set hints for this expression.
3653
3654        Examples:
3655            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3656            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3657
3658        Args:
3659            hints: The SQL code strings to parse as the hints.
3660                If an `Expression` instance is passed, it will be used as-is.
3661            dialect: The dialect used to parse the hints.
3662            copy: If `False`, modify this expression instance in-place.
3663
3664        Returns:
3665            The modified expression.
3666        """
3667        inst = maybe_copy(self, copy)
3668        inst.set(
3669            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3670        )
3671
3672        return inst
3673
3674    @property
3675    def named_selects(self) -> t.List[str]:
3676        return [e.output_name for e in self.expressions if e.alias_or_name]
3677
3678    @property
3679    def is_star(self) -> bool:
3680        return any(expression.is_star for expression in self.expressions)
3681
3682    @property
3683    def selects(self) -> t.List[Expression]:
3684        return self.expressions
3685
3686
3687UNWRAPPED_QUERIES = (Select, Union)
3688
3689
3690class Subquery(DerivedTable, Query):
3691    arg_types = {
3692        "this": True,
3693        "alias": False,
3694        "with": False,
3695        **QUERY_MODIFIERS,
3696    }
3697
3698    def unnest(self):
3699        """Returns the first non subquery."""
3700        expression = self
3701        while isinstance(expression, Subquery):
3702            expression = expression.this
3703        return expression
3704
3705    def unwrap(self) -> Subquery:
3706        expression = self
3707        while expression.same_parent and expression.is_wrapper:
3708            expression = t.cast(Subquery, expression.parent)
3709        return expression
3710
3711    def select(
3712        self,
3713        *expressions: t.Optional[ExpOrStr],
3714        append: bool = True,
3715        dialect: DialectType = None,
3716        copy: bool = True,
3717        **opts,
3718    ) -> Subquery:
3719        this = maybe_copy(self, copy)
3720        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3721        return this
3722
3723    @property
3724    def is_wrapper(self) -> bool:
3725        """
3726        Whether this Subquery acts as a simple wrapper around another expression.
3727
3728        SELECT * FROM (((SELECT * FROM t)))
3729                      ^
3730                      This corresponds to a "wrapper" Subquery node
3731        """
3732        return all(v is None for k, v in self.args.items() if k != "this")
3733
3734    @property
3735    def is_star(self) -> bool:
3736        return self.this.is_star
3737
3738    @property
3739    def output_name(self) -> str:
3740        return self.alias
3741
3742
3743class TableSample(Expression):
3744    arg_types = {
3745        "this": False,
3746        "expressions": False,
3747        "method": False,
3748        "bucket_numerator": False,
3749        "bucket_denominator": False,
3750        "bucket_field": False,
3751        "percent": False,
3752        "rows": False,
3753        "size": False,
3754        "seed": False,
3755    }
3756
3757
3758class Tag(Expression):
3759    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3760
3761    arg_types = {
3762        "this": False,
3763        "prefix": False,
3764        "postfix": False,
3765    }
3766
3767
3768# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3769# https://duckdb.org/docs/sql/statements/pivot
3770class Pivot(Expression):
3771    arg_types = {
3772        "this": False,
3773        "alias": False,
3774        "expressions": False,
3775        "field": False,
3776        "unpivot": False,
3777        "using": False,
3778        "group": False,
3779        "columns": False,
3780        "include_nulls": False,
3781    }
3782
3783    @property
3784    def unpivot(self) -> bool:
3785        return bool(self.args.get("unpivot"))
3786
3787
3788class Window(Condition):
3789    arg_types = {
3790        "this": True,
3791        "partition_by": False,
3792        "order": False,
3793        "spec": False,
3794        "alias": False,
3795        "over": False,
3796        "first": False,
3797    }
3798
3799
3800class WindowSpec(Expression):
3801    arg_types = {
3802        "kind": False,
3803        "start": False,
3804        "start_side": False,
3805        "end": False,
3806        "end_side": False,
3807    }
3808
3809
3810class PreWhere(Expression):
3811    pass
3812
3813
3814class Where(Expression):
3815    pass
3816
3817
3818class Star(Expression):
3819    arg_types = {"except": False, "replace": False}
3820
3821    @property
3822    def name(self) -> str:
3823        return "*"
3824
3825    @property
3826    def output_name(self) -> str:
3827        return self.name
3828
3829
3830class Parameter(Condition):
3831    arg_types = {"this": True, "expression": False}
3832
3833
3834class SessionParameter(Condition):
3835    arg_types = {"this": True, "kind": False}
3836
3837
3838class Placeholder(Condition):
3839    arg_types = {"this": False, "kind": False}
3840
3841    @property
3842    def name(self) -> str:
3843        return self.this or "?"
3844
3845
3846class Null(Condition):
3847    arg_types: t.Dict[str, t.Any] = {}
3848
3849    @property
3850    def name(self) -> str:
3851        return "NULL"
3852
3853
3854class Boolean(Condition):
3855    pass
3856
3857
3858class DataTypeParam(Expression):
3859    arg_types = {"this": True, "expression": False}
3860
3861    @property
3862    def name(self) -> str:
3863        return self.this.name
3864
3865
3866class DataType(Expression):
3867    arg_types = {
3868        "this": True,
3869        "expressions": False,
3870        "nested": False,
3871        "values": False,
3872        "prefix": False,
3873        "kind": False,
3874    }
3875
3876    class Type(AutoName):
3877        ARRAY = auto()
3878        AGGREGATEFUNCTION = auto()
3879        SIMPLEAGGREGATEFUNCTION = auto()
3880        BIGDECIMAL = auto()
3881        BIGINT = auto()
3882        BIGSERIAL = auto()
3883        BINARY = auto()
3884        BIT = auto()
3885        BOOLEAN = auto()
3886        BPCHAR = auto()
3887        CHAR = auto()
3888        DATE = auto()
3889        DATE32 = auto()
3890        DATEMULTIRANGE = auto()
3891        DATERANGE = auto()
3892        DATETIME = auto()
3893        DATETIME64 = auto()
3894        DECIMAL = auto()
3895        DOUBLE = auto()
3896        ENUM = auto()
3897        ENUM8 = auto()
3898        ENUM16 = auto()
3899        FIXEDSTRING = auto()
3900        FLOAT = auto()
3901        GEOGRAPHY = auto()
3902        GEOMETRY = auto()
3903        HLLSKETCH = auto()
3904        HSTORE = auto()
3905        IMAGE = auto()
3906        INET = auto()
3907        INT = auto()
3908        INT128 = auto()
3909        INT256 = auto()
3910        INT4MULTIRANGE = auto()
3911        INT4RANGE = auto()
3912        INT8MULTIRANGE = auto()
3913        INT8RANGE = auto()
3914        INTERVAL = auto()
3915        IPADDRESS = auto()
3916        IPPREFIX = auto()
3917        IPV4 = auto()
3918        IPV6 = auto()
3919        JSON = auto()
3920        JSONB = auto()
3921        LONGBLOB = auto()
3922        LONGTEXT = auto()
3923        LOWCARDINALITY = auto()
3924        MAP = auto()
3925        MEDIUMBLOB = auto()
3926        MEDIUMINT = auto()
3927        MEDIUMTEXT = auto()
3928        MONEY = auto()
3929        NAME = auto()
3930        NCHAR = auto()
3931        NESTED = auto()
3932        NULL = auto()
3933        NULLABLE = auto()
3934        NUMMULTIRANGE = auto()
3935        NUMRANGE = auto()
3936        NVARCHAR = auto()
3937        OBJECT = auto()
3938        ROWVERSION = auto()
3939        SERIAL = auto()
3940        SET = auto()
3941        SMALLINT = auto()
3942        SMALLMONEY = auto()
3943        SMALLSERIAL = auto()
3944        STRUCT = auto()
3945        SUPER = auto()
3946        TEXT = auto()
3947        TINYBLOB = auto()
3948        TINYTEXT = auto()
3949        TIME = auto()
3950        TIMETZ = auto()
3951        TIMESTAMP = auto()
3952        TIMESTAMPNTZ = auto()
3953        TIMESTAMPLTZ = auto()
3954        TIMESTAMPTZ = auto()
3955        TIMESTAMP_S = auto()
3956        TIMESTAMP_MS = auto()
3957        TIMESTAMP_NS = auto()
3958        TINYINT = auto()
3959        TSMULTIRANGE = auto()
3960        TSRANGE = auto()
3961        TSTZMULTIRANGE = auto()
3962        TSTZRANGE = auto()
3963        UBIGINT = auto()
3964        UINT = auto()
3965        UINT128 = auto()
3966        UINT256 = auto()
3967        UMEDIUMINT = auto()
3968        UDECIMAL = auto()
3969        UNIQUEIDENTIFIER = auto()
3970        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3971        USERDEFINED = "USER-DEFINED"
3972        USMALLINT = auto()
3973        UTINYINT = auto()
3974        UUID = auto()
3975        VARBINARY = auto()
3976        VARCHAR = auto()
3977        VARIANT = auto()
3978        XML = auto()
3979        YEAR = auto()
3980        TDIGEST = auto()
3981
3982    STRUCT_TYPES = {
3983        Type.NESTED,
3984        Type.OBJECT,
3985        Type.STRUCT,
3986    }
3987
3988    NESTED_TYPES = {
3989        *STRUCT_TYPES,
3990        Type.ARRAY,
3991        Type.MAP,
3992    }
3993
3994    TEXT_TYPES = {
3995        Type.CHAR,
3996        Type.NCHAR,
3997        Type.NVARCHAR,
3998        Type.TEXT,
3999        Type.VARCHAR,
4000        Type.NAME,
4001    }
4002
4003    SIGNED_INTEGER_TYPES = {
4004        Type.BIGINT,
4005        Type.INT,
4006        Type.INT128,
4007        Type.INT256,
4008        Type.MEDIUMINT,
4009        Type.SMALLINT,
4010        Type.TINYINT,
4011    }
4012
4013    UNSIGNED_INTEGER_TYPES = {
4014        Type.UBIGINT,
4015        Type.UINT,
4016        Type.UINT128,
4017        Type.UINT256,
4018        Type.UMEDIUMINT,
4019        Type.USMALLINT,
4020        Type.UTINYINT,
4021    }
4022
4023    INTEGER_TYPES = {
4024        *SIGNED_INTEGER_TYPES,
4025        *UNSIGNED_INTEGER_TYPES,
4026        Type.BIT,
4027    }
4028
4029    FLOAT_TYPES = {
4030        Type.DOUBLE,
4031        Type.FLOAT,
4032    }
4033
4034    REAL_TYPES = {
4035        *FLOAT_TYPES,
4036        Type.BIGDECIMAL,
4037        Type.DECIMAL,
4038        Type.MONEY,
4039        Type.SMALLMONEY,
4040        Type.UDECIMAL,
4041    }
4042
4043    NUMERIC_TYPES = {
4044        *INTEGER_TYPES,
4045        *REAL_TYPES,
4046    }
4047
4048    TEMPORAL_TYPES = {
4049        Type.DATE,
4050        Type.DATE32,
4051        Type.DATETIME,
4052        Type.DATETIME64,
4053        Type.TIME,
4054        Type.TIMESTAMP,
4055        Type.TIMESTAMPNTZ,
4056        Type.TIMESTAMPLTZ,
4057        Type.TIMESTAMPTZ,
4058        Type.TIMESTAMP_MS,
4059        Type.TIMESTAMP_NS,
4060        Type.TIMESTAMP_S,
4061        Type.TIMETZ,
4062    }
4063
4064    @classmethod
4065    def build(
4066        cls,
4067        dtype: DATA_TYPE,
4068        dialect: DialectType = None,
4069        udt: bool = False,
4070        copy: bool = True,
4071        **kwargs,
4072    ) -> DataType:
4073        """
4074        Constructs a DataType object.
4075
4076        Args:
4077            dtype: the data type of interest.
4078            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4079            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4080                DataType, thus creating a user-defined type.
4081            copy: whether to copy the data type.
4082            kwargs: additional arguments to pass in the constructor of DataType.
4083
4084        Returns:
4085            The constructed DataType object.
4086        """
4087        from sqlglot import parse_one
4088
4089        if isinstance(dtype, str):
4090            if dtype.upper() == "UNKNOWN":
4091                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4092
4093            try:
4094                data_type_exp = parse_one(
4095                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4096                )
4097            except ParseError:
4098                if udt:
4099                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4100                raise
4101        elif isinstance(dtype, DataType.Type):
4102            data_type_exp = DataType(this=dtype)
4103        elif isinstance(dtype, DataType):
4104            return maybe_copy(dtype, copy)
4105        else:
4106            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4107
4108        return DataType(**{**data_type_exp.args, **kwargs})
4109
4110    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4111        """
4112        Checks whether this DataType matches one of the provided data types. Nested types or precision
4113        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4114
4115        Args:
4116            dtypes: the data types to compare this DataType to.
4117
4118        Returns:
4119            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4120        """
4121        for dtype in dtypes:
4122            other = DataType.build(dtype, copy=False, udt=True)
4123
4124            if (
4125                other.expressions
4126                or self.this == DataType.Type.USERDEFINED
4127                or other.this == DataType.Type.USERDEFINED
4128            ):
4129                matches = self == other
4130            else:
4131                matches = self.this == other.this
4132
4133            if matches:
4134                return True
4135        return False
4136
4137
4138DATA_TYPE = t.Union[str, DataType, DataType.Type]
4139
4140
4141# https://www.postgresql.org/docs/15/datatype-pseudo.html
4142class PseudoType(DataType):
4143    arg_types = {"this": True}
4144
4145
4146# https://www.postgresql.org/docs/15/datatype-oid.html
4147class ObjectIdentifier(DataType):
4148    arg_types = {"this": True}
4149
4150
4151# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4152class SubqueryPredicate(Predicate):
4153    pass
4154
4155
4156class All(SubqueryPredicate):
4157    pass
4158
4159
4160class Any(SubqueryPredicate):
4161    pass
4162
4163
4164class Exists(SubqueryPredicate):
4165    pass
4166
4167
4168# Commands to interact with the databases or engines. For most of the command
4169# expressions we parse whatever comes after the command's name as a string.
4170class Command(Expression):
4171    arg_types = {"this": True, "expression": False}
4172
4173
4174class Transaction(Expression):
4175    arg_types = {"this": False, "modes": False, "mark": False}
4176
4177
4178class Commit(Expression):
4179    arg_types = {"chain": False, "this": False, "durability": False}
4180
4181
4182class Rollback(Expression):
4183    arg_types = {"savepoint": False, "this": False}
4184
4185
4186class AlterTable(Expression):
4187    arg_types = {
4188        "this": True,
4189        "actions": True,
4190        "exists": False,
4191        "only": False,
4192        "options": False,
4193        "cluster": False,
4194    }
4195
4196
4197class AddConstraint(Expression):
4198    arg_types = {"expressions": True}
4199
4200
4201class DropPartition(Expression):
4202    arg_types = {"expressions": True, "exists": False}
4203
4204
4205# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#replace-partition
4206class ReplacePartition(Expression):
4207    arg_types = {"expression": True, "source": True}
4208
4209
4210# Binary expressions like (ADD a b)
4211class Binary(Condition):
4212    arg_types = {"this": True, "expression": True}
4213
4214    @property
4215    def left(self) -> Expression:
4216        return self.this
4217
4218    @property
4219    def right(self) -> Expression:
4220        return self.expression
4221
4222
4223class Add(Binary):
4224    pass
4225
4226
4227class Connector(Binary):
4228    pass
4229
4230
4231class And(Connector):
4232    pass
4233
4234
4235class Or(Connector):
4236    pass
4237
4238
4239class BitwiseAnd(Binary):
4240    pass
4241
4242
4243class BitwiseLeftShift(Binary):
4244    pass
4245
4246
4247class BitwiseOr(Binary):
4248    pass
4249
4250
4251class BitwiseRightShift(Binary):
4252    pass
4253
4254
4255class BitwiseXor(Binary):
4256    pass
4257
4258
4259class Div(Binary):
4260    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4261
4262
4263class Overlaps(Binary):
4264    pass
4265
4266
4267class Dot(Binary):
4268    @property
4269    def is_star(self) -> bool:
4270        return self.expression.is_star
4271
4272    @property
4273    def name(self) -> str:
4274        return self.expression.name
4275
4276    @property
4277    def output_name(self) -> str:
4278        return self.name
4279
4280    @classmethod
4281    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4282        """Build a Dot object with a sequence of expressions."""
4283        if len(expressions) < 2:
4284            raise ValueError("Dot requires >= 2 expressions.")
4285
4286        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4287
4288    @property
4289    def parts(self) -> t.List[Expression]:
4290        """Return the parts of a table / column in order catalog, db, table."""
4291        this, *parts = self.flatten()
4292
4293        parts.reverse()
4294
4295        for arg in COLUMN_PARTS:
4296            part = this.args.get(arg)
4297
4298            if isinstance(part, Expression):
4299                parts.append(part)
4300
4301        parts.reverse()
4302        return parts
4303
4304
4305class DPipe(Binary):
4306    arg_types = {"this": True, "expression": True, "safe": False}
4307
4308
4309class EQ(Binary, Predicate):
4310    pass
4311
4312
4313class NullSafeEQ(Binary, Predicate):
4314    pass
4315
4316
4317class NullSafeNEQ(Binary, Predicate):
4318    pass
4319
4320
4321# Represents e.g. := in DuckDB which is mostly used for setting parameters
4322class PropertyEQ(Binary):
4323    pass
4324
4325
4326class Distance(Binary):
4327    pass
4328
4329
4330class Escape(Binary):
4331    pass
4332
4333
4334class Glob(Binary, Predicate):
4335    pass
4336
4337
4338class GT(Binary, Predicate):
4339    pass
4340
4341
4342class GTE(Binary, Predicate):
4343    pass
4344
4345
4346class ILike(Binary, Predicate):
4347    pass
4348
4349
4350class ILikeAny(Binary, Predicate):
4351    pass
4352
4353
4354class IntDiv(Binary):
4355    pass
4356
4357
4358class Is(Binary, Predicate):
4359    pass
4360
4361
4362class Kwarg(Binary):
4363    """Kwarg in special functions like func(kwarg => y)."""
4364
4365
4366class Like(Binary, Predicate):
4367    pass
4368
4369
4370class LikeAny(Binary, Predicate):
4371    pass
4372
4373
4374class LT(Binary, Predicate):
4375    pass
4376
4377
4378class LTE(Binary, Predicate):
4379    pass
4380
4381
4382class Mod(Binary):
4383    pass
4384
4385
4386class Mul(Binary):
4387    pass
4388
4389
4390class NEQ(Binary, Predicate):
4391    pass
4392
4393
4394# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4395class Operator(Binary):
4396    arg_types = {"this": True, "operator": True, "expression": True}
4397
4398
4399class SimilarTo(Binary, Predicate):
4400    pass
4401
4402
4403class Slice(Binary):
4404    arg_types = {"this": False, "expression": False}
4405
4406
4407class Sub(Binary):
4408    pass
4409
4410
4411# Unary Expressions
4412# (NOT a)
4413class Unary(Condition):
4414    pass
4415
4416
4417class BitwiseNot(Unary):
4418    pass
4419
4420
4421class Not(Unary):
4422    pass
4423
4424
4425class Paren(Unary):
4426    @property
4427    def output_name(self) -> str:
4428        return self.this.name
4429
4430
4431class Neg(Unary):
4432    pass
4433
4434
4435class Alias(Expression):
4436    arg_types = {"this": True, "alias": False}
4437
4438    @property
4439    def output_name(self) -> str:
4440        return self.alias
4441
4442
4443# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4444# other dialects require identifiers. This enables us to transpile between them easily.
4445class PivotAlias(Alias):
4446    pass
4447
4448
4449class Aliases(Expression):
4450    arg_types = {"this": True, "expressions": True}
4451
4452    @property
4453    def aliases(self):
4454        return self.expressions
4455
4456
4457# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4458class AtIndex(Expression):
4459    arg_types = {"this": True, "expression": True}
4460
4461
4462class AtTimeZone(Expression):
4463    arg_types = {"this": True, "zone": True}
4464
4465
4466class FromTimeZone(Expression):
4467    arg_types = {"this": True, "zone": True}
4468
4469
4470class Between(Predicate):
4471    arg_types = {"this": True, "low": True, "high": True}
4472
4473
4474class Bracket(Condition):
4475    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4476    arg_types = {
4477        "this": True,
4478        "expressions": True,
4479        "offset": False,
4480        "safe": False,
4481        "returns_list_for_maps": False,
4482    }
4483
4484    @property
4485    def output_name(self) -> str:
4486        if len(self.expressions) == 1:
4487            return self.expressions[0].output_name
4488
4489        return super().output_name
4490
4491
4492class Distinct(Expression):
4493    arg_types = {"expressions": False, "on": False}
4494
4495
4496class In(Predicate):
4497    arg_types = {
4498        "this": True,
4499        "expressions": False,
4500        "query": False,
4501        "unnest": False,
4502        "field": False,
4503        "is_global": False,
4504    }
4505
4506
4507# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4508class ForIn(Expression):
4509    arg_types = {"this": True, "expression": True}
4510
4511
4512class TimeUnit(Expression):
4513    """Automatically converts unit arg into a var."""
4514
4515    arg_types = {"unit": False}
4516
4517    UNABBREVIATED_UNIT_NAME = {
4518        "D": "DAY",
4519        "H": "HOUR",
4520        "M": "MINUTE",
4521        "MS": "MILLISECOND",
4522        "NS": "NANOSECOND",
4523        "Q": "QUARTER",
4524        "S": "SECOND",
4525        "US": "MICROSECOND",
4526        "W": "WEEK",
4527        "Y": "YEAR",
4528    }
4529
4530    VAR_LIKE = (Column, Literal, Var)
4531
4532    def __init__(self, **args):
4533        unit = args.get("unit")
4534        if isinstance(unit, self.VAR_LIKE):
4535            args["unit"] = Var(
4536                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4537            )
4538        elif isinstance(unit, Week):
4539            unit.set("this", Var(this=unit.this.name.upper()))
4540
4541        super().__init__(**args)
4542
4543    @property
4544    def unit(self) -> t.Optional[Var | IntervalSpan]:
4545        return self.args.get("unit")
4546
4547
4548class IntervalOp(TimeUnit):
4549    arg_types = {"unit": True, "expression": True}
4550
4551    def interval(self):
4552        return Interval(
4553            this=self.expression.copy(),
4554            unit=self.unit.copy(),
4555        )
4556
4557
4558# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4559# https://trino.io/docs/current/language/types.html#interval-day-to-second
4560# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4561class IntervalSpan(DataType):
4562    arg_types = {"this": True, "expression": True}
4563
4564
4565class Interval(TimeUnit):
4566    arg_types = {"this": False, "unit": False}
4567
4568
4569class IgnoreNulls(Expression):
4570    pass
4571
4572
4573class RespectNulls(Expression):
4574    pass
4575
4576
4577# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4578class HavingMax(Expression):
4579    arg_types = {"this": True, "expression": True, "max": True}
4580
4581
4582# Functions
4583class Func(Condition):
4584    """
4585    The base class for all function expressions.
4586
4587    Attributes:
4588        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4589            treated as a variable length argument and the argument's value will be stored as a list.
4590        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4591            function expression. These values are used to map this node to a name during parsing as
4592            well as to provide the function's name during SQL string generation. By default the SQL
4593            name is set to the expression's class name transformed to snake case.
4594    """
4595
4596    is_var_len_args = False
4597
4598    @classmethod
4599    def from_arg_list(cls, args):
4600        if cls.is_var_len_args:
4601            all_arg_keys = list(cls.arg_types)
4602            # If this function supports variable length argument treat the last argument as such.
4603            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4604            num_non_var = len(non_var_len_arg_keys)
4605
4606            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4607            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4608        else:
4609            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4610
4611        return cls(**args_dict)
4612
4613    @classmethod
4614    def sql_names(cls):
4615        if cls is Func:
4616            raise NotImplementedError(
4617                "SQL name is only supported by concrete function implementations"
4618            )
4619        if "_sql_names" not in cls.__dict__:
4620            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4621        return cls._sql_names
4622
4623    @classmethod
4624    def sql_name(cls):
4625        return cls.sql_names()[0]
4626
4627    @classmethod
4628    def default_parser_mappings(cls):
4629        return {name: cls.from_arg_list for name in cls.sql_names()}
4630
4631
4632class AggFunc(Func):
4633    pass
4634
4635
4636class ParameterizedAgg(AggFunc):
4637    arg_types = {"this": True, "expressions": True, "params": True}
4638
4639
4640class Abs(Func):
4641    pass
4642
4643
4644class ArgMax(AggFunc):
4645    arg_types = {"this": True, "expression": True, "count": False}
4646    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4647
4648
4649class ArgMin(AggFunc):
4650    arg_types = {"this": True, "expression": True, "count": False}
4651    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4652
4653
4654class ApproxTopK(AggFunc):
4655    arg_types = {"this": True, "expression": False, "counters": False}
4656
4657
4658class Flatten(Func):
4659    pass
4660
4661
4662# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4663class Transform(Func):
4664    arg_types = {"this": True, "expression": True}
4665
4666
4667class Anonymous(Func):
4668    arg_types = {"this": True, "expressions": False}
4669    is_var_len_args = True
4670
4671    @property
4672    def name(self) -> str:
4673        return self.this if isinstance(self.this, str) else self.this.name
4674
4675
4676class AnonymousAggFunc(AggFunc):
4677    arg_types = {"this": True, "expressions": False}
4678    is_var_len_args = True
4679
4680
4681# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4682class CombinedAggFunc(AnonymousAggFunc):
4683    arg_types = {"this": True, "expressions": False, "parts": True}
4684
4685
4686class CombinedParameterizedAgg(ParameterizedAgg):
4687    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4688
4689
4690# https://docs.snowflake.com/en/sql-reference/functions/hll
4691# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4692class Hll(AggFunc):
4693    arg_types = {"this": True, "expressions": False}
4694    is_var_len_args = True
4695
4696
4697class ApproxDistinct(AggFunc):
4698    arg_types = {"this": True, "accuracy": False}
4699    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4700
4701
4702class Array(Func):
4703    arg_types = {"expressions": False}
4704    is_var_len_args = True
4705
4706
4707# https://docs.snowflake.com/en/sql-reference/functions/to_array
4708class ToArray(Func):
4709    pass
4710
4711
4712# https://docs.snowflake.com/en/sql-reference/functions/to_char
4713# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4714class ToChar(Func):
4715    arg_types = {"this": True, "format": False, "nlsparam": False}
4716
4717
4718# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4719# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4720class ToNumber(Func):
4721    arg_types = {
4722        "this": True,
4723        "format": False,
4724        "nlsparam": False,
4725        "precision": False,
4726        "scale": False,
4727    }
4728
4729
4730# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4731class Convert(Func):
4732    arg_types = {"this": True, "expression": True, "style": False}
4733
4734
4735class GenerateSeries(Func):
4736    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4737
4738
4739class ArrayAgg(AggFunc):
4740    pass
4741
4742
4743class ArrayUniqueAgg(AggFunc):
4744    pass
4745
4746
4747class ArrayAll(Func):
4748    arg_types = {"this": True, "expression": True}
4749
4750
4751# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4752class ArrayAny(Func):
4753    arg_types = {"this": True, "expression": True}
4754
4755
4756class ArrayConcat(Func):
4757    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4758    arg_types = {"this": True, "expressions": False}
4759    is_var_len_args = True
4760
4761
4762class ArrayContains(Binary, Func):
4763    pass
4764
4765
4766class ArrayContained(Binary):
4767    pass
4768
4769
4770class ArrayFilter(Func):
4771    arg_types = {"this": True, "expression": True}
4772    _sql_names = ["FILTER", "ARRAY_FILTER"]
4773
4774
4775class ArrayToString(Func):
4776    arg_types = {"this": True, "expression": True, "null": False}
4777    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4778
4779
4780class ArrayOverlaps(Binary, Func):
4781    pass
4782
4783
4784class ArraySize(Func):
4785    arg_types = {"this": True, "expression": False}
4786    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4787
4788
4789class ArraySort(Func):
4790    arg_types = {"this": True, "expression": False}
4791
4792
4793class ArraySum(Func):
4794    arg_types = {"this": True, "expression": False}
4795
4796
4797class ArrayUnionAgg(AggFunc):
4798    pass
4799
4800
4801class Avg(AggFunc):
4802    pass
4803
4804
4805class AnyValue(AggFunc):
4806    pass
4807
4808
4809class Lag(AggFunc):
4810    arg_types = {"this": True, "offset": False, "default": False}
4811
4812
4813class Lead(AggFunc):
4814    arg_types = {"this": True, "offset": False, "default": False}
4815
4816
4817# some dialects have a distinction between first and first_value, usually first is an aggregate func
4818# and first_value is a window func
4819class First(AggFunc):
4820    pass
4821
4822
4823class Last(AggFunc):
4824    pass
4825
4826
4827class FirstValue(AggFunc):
4828    pass
4829
4830
4831class LastValue(AggFunc):
4832    pass
4833
4834
4835class NthValue(AggFunc):
4836    arg_types = {"this": True, "offset": True}
4837
4838
4839class Case(Func):
4840    arg_types = {"this": False, "ifs": True, "default": False}
4841
4842    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4843        instance = maybe_copy(self, copy)
4844        instance.append(
4845            "ifs",
4846            If(
4847                this=maybe_parse(condition, copy=copy, **opts),
4848                true=maybe_parse(then, copy=copy, **opts),
4849            ),
4850        )
4851        return instance
4852
4853    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4854        instance = maybe_copy(self, copy)
4855        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4856        return instance
4857
4858
4859class Cast(Func):
4860    arg_types = {
4861        "this": True,
4862        "to": True,
4863        "format": False,
4864        "safe": False,
4865        "action": False,
4866    }
4867
4868    @property
4869    def name(self) -> str:
4870        return self.this.name
4871
4872    @property
4873    def to(self) -> DataType:
4874        return self.args["to"]
4875
4876    @property
4877    def output_name(self) -> str:
4878        return self.name
4879
4880    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4881        """
4882        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4883        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4884        array<int> != array<float>.
4885
4886        Args:
4887            dtypes: the data types to compare this Cast's DataType to.
4888
4889        Returns:
4890            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4891        """
4892        return self.to.is_type(*dtypes)
4893
4894
4895class TryCast(Cast):
4896    pass
4897
4898
4899class Try(Func):
4900    pass
4901
4902
4903class CastToStrType(Func):
4904    arg_types = {"this": True, "to": True}
4905
4906
4907class Collate(Binary, Func):
4908    pass
4909
4910
4911class Ceil(Func):
4912    arg_types = {"this": True, "decimals": False}
4913    _sql_names = ["CEIL", "CEILING"]
4914
4915
4916class Coalesce(Func):
4917    arg_types = {"this": True, "expressions": False}
4918    is_var_len_args = True
4919    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4920
4921
4922class Chr(Func):
4923    arg_types = {"this": True, "charset": False, "expressions": False}
4924    is_var_len_args = True
4925    _sql_names = ["CHR", "CHAR"]
4926
4927
4928class Concat(Func):
4929    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4930    is_var_len_args = True
4931
4932
4933class ConcatWs(Concat):
4934    _sql_names = ["CONCAT_WS"]
4935
4936
4937# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4938class ConnectByRoot(Func):
4939    pass
4940
4941
4942class Count(AggFunc):
4943    arg_types = {"this": False, "expressions": False}
4944    is_var_len_args = True
4945
4946
4947class CountIf(AggFunc):
4948    _sql_names = ["COUNT_IF", "COUNTIF"]
4949
4950
4951# cube root
4952class Cbrt(Func):
4953    pass
4954
4955
4956class CurrentDate(Func):
4957    arg_types = {"this": False}
4958
4959
4960class CurrentDatetime(Func):
4961    arg_types = {"this": False}
4962
4963
4964class CurrentTime(Func):
4965    arg_types = {"this": False}
4966
4967
4968class CurrentTimestamp(Func):
4969    arg_types = {"this": False, "transaction": False}
4970
4971
4972class CurrentUser(Func):
4973    arg_types = {"this": False}
4974
4975
4976class DateAdd(Func, IntervalOp):
4977    arg_types = {"this": True, "expression": True, "unit": False}
4978
4979
4980class DateSub(Func, IntervalOp):
4981    arg_types = {"this": True, "expression": True, "unit": False}
4982
4983
4984class DateDiff(Func, TimeUnit):
4985    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4986    arg_types = {"this": True, "expression": True, "unit": False}
4987
4988
4989class DateTrunc(Func):
4990    arg_types = {"unit": True, "this": True, "zone": False}
4991
4992    def __init__(self, **args):
4993        unit = args.get("unit")
4994        if isinstance(unit, TimeUnit.VAR_LIKE):
4995            args["unit"] = Literal.string(
4996                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4997            )
4998        elif isinstance(unit, Week):
4999            unit.set("this", Literal.string(unit.this.name.upper()))
5000
5001        super().__init__(**args)
5002
5003    @property
5004    def unit(self) -> Expression:
5005        return self.args["unit"]
5006
5007
5008class DatetimeAdd(Func, IntervalOp):
5009    arg_types = {"this": True, "expression": True, "unit": False}
5010
5011
5012class DatetimeSub(Func, IntervalOp):
5013    arg_types = {"this": True, "expression": True, "unit": False}
5014
5015
5016class DatetimeDiff(Func, TimeUnit):
5017    arg_types = {"this": True, "expression": True, "unit": False}
5018
5019
5020class DatetimeTrunc(Func, TimeUnit):
5021    arg_types = {"this": True, "unit": True, "zone": False}
5022
5023
5024class DayOfWeek(Func):
5025    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5026
5027
5028class DayOfMonth(Func):
5029    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5030
5031
5032class DayOfYear(Func):
5033    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5034
5035
5036class ToDays(Func):
5037    pass
5038
5039
5040class WeekOfYear(Func):
5041    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5042
5043
5044class MonthsBetween(Func):
5045    arg_types = {"this": True, "expression": True, "roundoff": False}
5046
5047
5048class LastDay(Func, TimeUnit):
5049    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5050    arg_types = {"this": True, "unit": False}
5051
5052
5053class Extract(Func):
5054    arg_types = {"this": True, "expression": True}
5055
5056
5057class Timestamp(Func):
5058    arg_types = {"this": False, "expression": False, "with_tz": False}
5059
5060
5061class TimestampAdd(Func, TimeUnit):
5062    arg_types = {"this": True, "expression": True, "unit": False}
5063
5064
5065class TimestampSub(Func, TimeUnit):
5066    arg_types = {"this": True, "expression": True, "unit": False}
5067
5068
5069class TimestampDiff(Func, TimeUnit):
5070    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5071    arg_types = {"this": True, "expression": True, "unit": False}
5072
5073
5074class TimestampTrunc(Func, TimeUnit):
5075    arg_types = {"this": True, "unit": True, "zone": False}
5076
5077
5078class TimeAdd(Func, TimeUnit):
5079    arg_types = {"this": True, "expression": True, "unit": False}
5080
5081
5082class TimeSub(Func, TimeUnit):
5083    arg_types = {"this": True, "expression": True, "unit": False}
5084
5085
5086class TimeDiff(Func, TimeUnit):
5087    arg_types = {"this": True, "expression": True, "unit": False}
5088
5089
5090class TimeTrunc(Func, TimeUnit):
5091    arg_types = {"this": True, "unit": True, "zone": False}
5092
5093
5094class DateFromParts(Func):
5095    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5096    arg_types = {"year": True, "month": True, "day": True}
5097
5098
5099class TimeFromParts(Func):
5100    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5101    arg_types = {
5102        "hour": True,
5103        "min": True,
5104        "sec": True,
5105        "nano": False,
5106        "fractions": False,
5107        "precision": False,
5108    }
5109
5110
5111class DateStrToDate(Func):
5112    pass
5113
5114
5115class DateToDateStr(Func):
5116    pass
5117
5118
5119class DateToDi(Func):
5120    pass
5121
5122
5123# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5124class Date(Func):
5125    arg_types = {"this": False, "zone": False, "expressions": False}
5126    is_var_len_args = True
5127
5128
5129class Day(Func):
5130    pass
5131
5132
5133class Decode(Func):
5134    arg_types = {"this": True, "charset": True, "replace": False}
5135
5136
5137class DiToDate(Func):
5138    pass
5139
5140
5141class Encode(Func):
5142    arg_types = {"this": True, "charset": True}
5143
5144
5145class Exp(Func):
5146    pass
5147
5148
5149# https://docs.snowflake.com/en/sql-reference/functions/flatten
5150class Explode(Func):
5151    arg_types = {"this": True, "expressions": False}
5152    is_var_len_args = True
5153
5154
5155class ExplodeOuter(Explode):
5156    pass
5157
5158
5159class Posexplode(Explode):
5160    pass
5161
5162
5163class PosexplodeOuter(Posexplode, ExplodeOuter):
5164    pass
5165
5166
5167class Floor(Func):
5168    arg_types = {"this": True, "decimals": False}
5169
5170
5171class FromBase64(Func):
5172    pass
5173
5174
5175class ToBase64(Func):
5176    pass
5177
5178
5179class GenerateDateArray(Func):
5180    arg_types = {"start": True, "end": True, "interval": False}
5181
5182
5183class Greatest(Func):
5184    arg_types = {"this": True, "expressions": False}
5185    is_var_len_args = True
5186
5187
5188class GroupConcat(AggFunc):
5189    arg_types = {"this": True, "separator": False}
5190
5191
5192class Hex(Func):
5193    pass
5194
5195
5196class Xor(Connector, Func):
5197    arg_types = {"this": False, "expression": False, "expressions": False}
5198
5199
5200class If(Func):
5201    arg_types = {"this": True, "true": True, "false": False}
5202    _sql_names = ["IF", "IIF"]
5203
5204
5205class Nullif(Func):
5206    arg_types = {"this": True, "expression": True}
5207
5208
5209class Initcap(Func):
5210    arg_types = {"this": True, "expression": False}
5211
5212
5213class IsNan(Func):
5214    _sql_names = ["IS_NAN", "ISNAN"]
5215
5216
5217class IsInf(Func):
5218    _sql_names = ["IS_INF", "ISINF"]
5219
5220
5221class JSONPath(Expression):
5222    arg_types = {"expressions": True}
5223
5224    @property
5225    def output_name(self) -> str:
5226        last_segment = self.expressions[-1].this
5227        return last_segment if isinstance(last_segment, str) else ""
5228
5229
5230class JSONPathPart(Expression):
5231    arg_types = {}
5232
5233
5234class JSONPathFilter(JSONPathPart):
5235    arg_types = {"this": True}
5236
5237
5238class JSONPathKey(JSONPathPart):
5239    arg_types = {"this": True}
5240
5241
5242class JSONPathRecursive(JSONPathPart):
5243    arg_types = {"this": False}
5244
5245
5246class JSONPathRoot(JSONPathPart):
5247    pass
5248
5249
5250class JSONPathScript(JSONPathPart):
5251    arg_types = {"this": True}
5252
5253
5254class JSONPathSlice(JSONPathPart):
5255    arg_types = {"start": False, "end": False, "step": False}
5256
5257
5258class JSONPathSelector(JSONPathPart):
5259    arg_types = {"this": True}
5260
5261
5262class JSONPathSubscript(JSONPathPart):
5263    arg_types = {"this": True}
5264
5265
5266class JSONPathUnion(JSONPathPart):
5267    arg_types = {"expressions": True}
5268
5269
5270class JSONPathWildcard(JSONPathPart):
5271    pass
5272
5273
5274class FormatJson(Expression):
5275    pass
5276
5277
5278class JSONKeyValue(Expression):
5279    arg_types = {"this": True, "expression": True}
5280
5281
5282class JSONObject(Func):
5283    arg_types = {
5284        "expressions": False,
5285        "null_handling": False,
5286        "unique_keys": False,
5287        "return_type": False,
5288        "encoding": False,
5289    }
5290
5291
5292class JSONObjectAgg(AggFunc):
5293    arg_types = {
5294        "expressions": False,
5295        "null_handling": False,
5296        "unique_keys": False,
5297        "return_type": False,
5298        "encoding": False,
5299    }
5300
5301
5302# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5303class JSONArray(Func):
5304    arg_types = {
5305        "expressions": True,
5306        "null_handling": False,
5307        "return_type": False,
5308        "strict": False,
5309    }
5310
5311
5312# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5313class JSONArrayAgg(Func):
5314    arg_types = {
5315        "this": True,
5316        "order": False,
5317        "null_handling": False,
5318        "return_type": False,
5319        "strict": False,
5320    }
5321
5322
5323# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5324# Note: parsing of JSON column definitions is currently incomplete.
5325class JSONColumnDef(Expression):
5326    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5327
5328
5329class JSONSchema(Expression):
5330    arg_types = {"expressions": True}
5331
5332
5333# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5334class JSONTable(Func):
5335    arg_types = {
5336        "this": True,
5337        "schema": True,
5338        "path": False,
5339        "error_handling": False,
5340        "empty_handling": False,
5341    }
5342
5343
5344class OpenJSONColumnDef(Expression):
5345    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5346
5347
5348class OpenJSON(Func):
5349    arg_types = {"this": True, "path": False, "expressions": False}
5350
5351
5352class JSONBContains(Binary):
5353    _sql_names = ["JSONB_CONTAINS"]
5354
5355
5356class JSONExtract(Binary, Func):
5357    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5358    _sql_names = ["JSON_EXTRACT"]
5359    is_var_len_args = True
5360
5361    @property
5362    def output_name(self) -> str:
5363        return self.expression.output_name if not self.expressions else ""
5364
5365
5366class JSONExtractScalar(Binary, Func):
5367    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5368    _sql_names = ["JSON_EXTRACT_SCALAR"]
5369    is_var_len_args = True
5370
5371    @property
5372    def output_name(self) -> str:
5373        return self.expression.output_name
5374
5375
5376class JSONBExtract(Binary, Func):
5377    _sql_names = ["JSONB_EXTRACT"]
5378
5379
5380class JSONBExtractScalar(Binary, Func):
5381    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5382
5383
5384class JSONFormat(Func):
5385    arg_types = {"this": False, "options": False}
5386    _sql_names = ["JSON_FORMAT"]
5387
5388
5389# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5390class JSONArrayContains(Binary, Predicate, Func):
5391    _sql_names = ["JSON_ARRAY_CONTAINS"]
5392
5393
5394class ParseJSON(Func):
5395    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5396    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5397    arg_types = {"this": True, "expressions": False}
5398    is_var_len_args = True
5399
5400
5401class Least(Func):
5402    arg_types = {"this": True, "expressions": False}
5403    is_var_len_args = True
5404
5405
5406class Left(Func):
5407    arg_types = {"this": True, "expression": True}
5408
5409
5410class Right(Func):
5411    arg_types = {"this": True, "expression": True}
5412
5413
5414class Length(Func):
5415    _sql_names = ["LENGTH", "LEN"]
5416
5417
5418class Levenshtein(Func):
5419    arg_types = {
5420        "this": True,
5421        "expression": False,
5422        "ins_cost": False,
5423        "del_cost": False,
5424        "sub_cost": False,
5425    }
5426
5427
5428class Ln(Func):
5429    pass
5430
5431
5432class Log(Func):
5433    arg_types = {"this": True, "expression": False}
5434
5435
5436class LogicalOr(AggFunc):
5437    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5438
5439
5440class LogicalAnd(AggFunc):
5441    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5442
5443
5444class Lower(Func):
5445    _sql_names = ["LOWER", "LCASE"]
5446
5447
5448class Map(Func):
5449    arg_types = {"keys": False, "values": False}
5450
5451    @property
5452    def keys(self) -> t.List[Expression]:
5453        keys = self.args.get("keys")
5454        return keys.expressions if keys else []
5455
5456    @property
5457    def values(self) -> t.List[Expression]:
5458        values = self.args.get("values")
5459        return values.expressions if values else []
5460
5461
5462# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5463class ToMap(Func):
5464    pass
5465
5466
5467class MapFromEntries(Func):
5468    pass
5469
5470
5471class StarMap(Func):
5472    pass
5473
5474
5475class VarMap(Func):
5476    arg_types = {"keys": True, "values": True}
5477    is_var_len_args = True
5478
5479    @property
5480    def keys(self) -> t.List[Expression]:
5481        return self.args["keys"].expressions
5482
5483    @property
5484    def values(self) -> t.List[Expression]:
5485        return self.args["values"].expressions
5486
5487
5488# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5489class MatchAgainst(Func):
5490    arg_types = {"this": True, "expressions": True, "modifier": False}
5491
5492
5493class Max(AggFunc):
5494    arg_types = {"this": True, "expressions": False}
5495    is_var_len_args = True
5496
5497
5498class MD5(Func):
5499    _sql_names = ["MD5"]
5500
5501
5502# Represents the variant of the MD5 function that returns a binary value
5503class MD5Digest(Func):
5504    _sql_names = ["MD5_DIGEST"]
5505
5506
5507class Min(AggFunc):
5508    arg_types = {"this": True, "expressions": False}
5509    is_var_len_args = True
5510
5511
5512class Month(Func):
5513    pass
5514
5515
5516class AddMonths(Func):
5517    arg_types = {"this": True, "expression": True}
5518
5519
5520class Nvl2(Func):
5521    arg_types = {"this": True, "true": True, "false": False}
5522
5523
5524# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5525class Predict(Func):
5526    arg_types = {"this": True, "expression": True, "params_struct": False}
5527
5528
5529class Pow(Binary, Func):
5530    _sql_names = ["POWER", "POW"]
5531
5532
5533class PercentileCont(AggFunc):
5534    arg_types = {"this": True, "expression": False}
5535
5536
5537class PercentileDisc(AggFunc):
5538    arg_types = {"this": True, "expression": False}
5539
5540
5541class Quantile(AggFunc):
5542    arg_types = {"this": True, "quantile": True}
5543
5544
5545class ApproxQuantile(Quantile):
5546    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5547
5548
5549class Quarter(Func):
5550    pass
5551
5552
5553class Rand(Func):
5554    _sql_names = ["RAND", "RANDOM"]
5555    arg_types = {"this": False}
5556
5557
5558class Randn(Func):
5559    arg_types = {"this": False}
5560
5561
5562class RangeN(Func):
5563    arg_types = {"this": True, "expressions": True, "each": False}
5564
5565
5566class ReadCSV(Func):
5567    _sql_names = ["READ_CSV"]
5568    is_var_len_args = True
5569    arg_types = {"this": True, "expressions": False}
5570
5571
5572class Reduce(Func):
5573    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5574
5575
5576class RegexpExtract(Func):
5577    arg_types = {
5578        "this": True,
5579        "expression": True,
5580        "position": False,
5581        "occurrence": False,
5582        "parameters": False,
5583        "group": False,
5584    }
5585
5586
5587class RegexpReplace(Func):
5588    arg_types = {
5589        "this": True,
5590        "expression": True,
5591        "replacement": False,
5592        "position": False,
5593        "occurrence": False,
5594        "modifiers": False,
5595    }
5596
5597
5598class RegexpLike(Binary, Func):
5599    arg_types = {"this": True, "expression": True, "flag": False}
5600
5601
5602class RegexpILike(Binary, Func):
5603    arg_types = {"this": True, "expression": True, "flag": False}
5604
5605
5606# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5607# limit is the number of times a pattern is applied
5608class RegexpSplit(Func):
5609    arg_types = {"this": True, "expression": True, "limit": False}
5610
5611
5612class Repeat(Func):
5613    arg_types = {"this": True, "times": True}
5614
5615
5616# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5617# tsql third argument function == trunctaion if not 0
5618class Round(Func):
5619    arg_types = {"this": True, "decimals": False, "truncate": False}
5620
5621
5622class RowNumber(Func):
5623    arg_types: t.Dict[str, t.Any] = {}
5624
5625
5626class SafeDivide(Func):
5627    arg_types = {"this": True, "expression": True}
5628
5629
5630class SHA(Func):
5631    _sql_names = ["SHA", "SHA1"]
5632
5633
5634class SHA2(Func):
5635    _sql_names = ["SHA2"]
5636    arg_types = {"this": True, "length": False}
5637
5638
5639class Sign(Func):
5640    _sql_names = ["SIGN", "SIGNUM"]
5641
5642
5643class SortArray(Func):
5644    arg_types = {"this": True, "asc": False}
5645
5646
5647class Split(Func):
5648    arg_types = {"this": True, "expression": True, "limit": False}
5649
5650
5651# Start may be omitted in the case of postgres
5652# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5653class Substring(Func):
5654    arg_types = {"this": True, "start": False, "length": False}
5655
5656
5657class StandardHash(Func):
5658    arg_types = {"this": True, "expression": False}
5659
5660
5661class StartsWith(Func):
5662    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5663    arg_types = {"this": True, "expression": True}
5664
5665
5666class StrPosition(Func):
5667    arg_types = {
5668        "this": True,
5669        "substr": True,
5670        "position": False,
5671        "instance": False,
5672    }
5673
5674
5675class StrToDate(Func):
5676    arg_types = {"this": True, "format": True}
5677
5678
5679class StrToTime(Func):
5680    arg_types = {"this": True, "format": True, "zone": False}
5681
5682
5683# Spark allows unix_timestamp()
5684# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5685class StrToUnix(Func):
5686    arg_types = {"this": False, "format": False}
5687
5688
5689# https://prestodb.io/docs/current/functions/string.html
5690# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5691class StrToMap(Func):
5692    arg_types = {
5693        "this": True,
5694        "pair_delim": False,
5695        "key_value_delim": False,
5696        "duplicate_resolution_callback": False,
5697    }
5698
5699
5700class NumberToStr(Func):
5701    arg_types = {"this": True, "format": True, "culture": False}
5702
5703
5704class FromBase(Func):
5705    arg_types = {"this": True, "expression": True}
5706
5707
5708class Struct(Func):
5709    arg_types = {"expressions": False}
5710    is_var_len_args = True
5711
5712
5713class StructExtract(Func):
5714    arg_types = {"this": True, "expression": True}
5715
5716
5717# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5718# https://docs.snowflake.com/en/sql-reference/functions/insert
5719class Stuff(Func):
5720    _sql_names = ["STUFF", "INSERT"]
5721    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5722
5723
5724class Sum(AggFunc):
5725    pass
5726
5727
5728class Sqrt(Func):
5729    pass
5730
5731
5732class Stddev(AggFunc):
5733    pass
5734
5735
5736class StddevPop(AggFunc):
5737    pass
5738
5739
5740class StddevSamp(AggFunc):
5741    pass
5742
5743
5744class TimeToStr(Func):
5745    arg_types = {"this": True, "format": True, "culture": False, "timezone": False}
5746
5747
5748class TimeToTimeStr(Func):
5749    pass
5750
5751
5752class TimeToUnix(Func):
5753    pass
5754
5755
5756class TimeStrToDate(Func):
5757    pass
5758
5759
5760class TimeStrToTime(Func):
5761    pass
5762
5763
5764class TimeStrToUnix(Func):
5765    pass
5766
5767
5768class Trim(Func):
5769    arg_types = {
5770        "this": True,
5771        "expression": False,
5772        "position": False,
5773        "collation": False,
5774    }
5775
5776
5777class TsOrDsAdd(Func, TimeUnit):
5778    # return_type is used to correctly cast the arguments of this expression when transpiling it
5779    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5780
5781    @property
5782    def return_type(self) -> DataType:
5783        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5784
5785
5786class TsOrDsDiff(Func, TimeUnit):
5787    arg_types = {"this": True, "expression": True, "unit": False}
5788
5789
5790class TsOrDsToDateStr(Func):
5791    pass
5792
5793
5794class TsOrDsToDate(Func):
5795    arg_types = {"this": True, "format": False, "safe": False}
5796
5797
5798class TsOrDsToTime(Func):
5799    pass
5800
5801
5802class TsOrDsToTimestamp(Func):
5803    pass
5804
5805
5806class TsOrDiToDi(Func):
5807    pass
5808
5809
5810class Unhex(Func):
5811    pass
5812
5813
5814# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5815class UnixDate(Func):
5816    pass
5817
5818
5819class UnixToStr(Func):
5820    arg_types = {"this": True, "format": False}
5821
5822
5823# https://prestodb.io/docs/current/functions/datetime.html
5824# presto has weird zone/hours/minutes
5825class UnixToTime(Func):
5826    arg_types = {
5827        "this": True,
5828        "scale": False,
5829        "zone": False,
5830        "hours": False,
5831        "minutes": False,
5832        "format": False,
5833    }
5834
5835    SECONDS = Literal.number(0)
5836    DECIS = Literal.number(1)
5837    CENTIS = Literal.number(2)
5838    MILLIS = Literal.number(3)
5839    DECIMILLIS = Literal.number(4)
5840    CENTIMILLIS = Literal.number(5)
5841    MICROS = Literal.number(6)
5842    DECIMICROS = Literal.number(7)
5843    CENTIMICROS = Literal.number(8)
5844    NANOS = Literal.number(9)
5845
5846
5847class UnixToTimeStr(Func):
5848    pass
5849
5850
5851class TimestampFromParts(Func):
5852    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5853    arg_types = {
5854        "year": True,
5855        "month": True,
5856        "day": True,
5857        "hour": True,
5858        "min": True,
5859        "sec": True,
5860        "nano": False,
5861        "zone": False,
5862        "milli": False,
5863    }
5864
5865
5866class Upper(Func):
5867    _sql_names = ["UPPER", "UCASE"]
5868
5869
5870class Corr(Binary, AggFunc):
5871    pass
5872
5873
5874class Variance(AggFunc):
5875    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5876
5877
5878class VariancePop(AggFunc):
5879    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5880
5881
5882class CovarSamp(Binary, AggFunc):
5883    pass
5884
5885
5886class CovarPop(Binary, AggFunc):
5887    pass
5888
5889
5890class Week(Func):
5891    arg_types = {"this": True, "mode": False}
5892
5893
5894class XMLTable(Func):
5895    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5896
5897
5898class Year(Func):
5899    pass
5900
5901
5902class Use(Expression):
5903    arg_types = {"this": True, "kind": False}
5904
5905
5906class Merge(Expression):
5907    arg_types = {
5908        "this": True,
5909        "using": True,
5910        "on": True,
5911        "expressions": True,
5912        "with": False,
5913    }
5914
5915
5916class When(Func):
5917    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5918
5919
5920# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5921# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5922class NextValueFor(Func):
5923    arg_types = {"this": True, "order": False}
5924
5925
5926# Refers to a trailing semi-colon. This is only used to preserve trailing comments
5927# select 1; -- my comment
5928class Semicolon(Expression):
5929    arg_types = {}
5930
5931
5932def _norm_arg(arg):
5933    return arg.lower() if type(arg) is str else arg
5934
5935
5936ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5937FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5938
5939JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5940
5941PERCENTILES = (PercentileCont, PercentileDisc)
5942
5943
5944# Helpers
5945@t.overload
5946def maybe_parse(
5947    sql_or_expression: ExpOrStr,
5948    *,
5949    into: t.Type[E],
5950    dialect: DialectType = None,
5951    prefix: t.Optional[str] = None,
5952    copy: bool = False,
5953    **opts,
5954) -> E: ...
5955
5956
5957@t.overload
5958def maybe_parse(
5959    sql_or_expression: str | E,
5960    *,
5961    into: t.Optional[IntoType] = None,
5962    dialect: DialectType = None,
5963    prefix: t.Optional[str] = None,
5964    copy: bool = False,
5965    **opts,
5966) -> E: ...
5967
5968
5969def maybe_parse(
5970    sql_or_expression: ExpOrStr,
5971    *,
5972    into: t.Optional[IntoType] = None,
5973    dialect: DialectType = None,
5974    prefix: t.Optional[str] = None,
5975    copy: bool = False,
5976    **opts,
5977) -> Expression:
5978    """Gracefully handle a possible string or expression.
5979
5980    Example:
5981        >>> maybe_parse("1")
5982        Literal(this=1, is_string=False)
5983        >>> maybe_parse(to_identifier("x"))
5984        Identifier(this=x, quoted=False)
5985
5986    Args:
5987        sql_or_expression: the SQL code string or an expression
5988        into: the SQLGlot Expression to parse into
5989        dialect: the dialect used to parse the input expressions (in the case that an
5990            input expression is a SQL string).
5991        prefix: a string to prefix the sql with before it gets parsed
5992            (automatically includes a space)
5993        copy: whether to copy the expression.
5994        **opts: other options to use to parse the input expressions (again, in the case
5995            that an input expression is a SQL string).
5996
5997    Returns:
5998        Expression: the parsed or given expression.
5999    """
6000    if isinstance(sql_or_expression, Expression):
6001        if copy:
6002            return sql_or_expression.copy()
6003        return sql_or_expression
6004
6005    if sql_or_expression is None:
6006        raise ParseError("SQL cannot be None")
6007
6008    import sqlglot
6009
6010    sql = str(sql_or_expression)
6011    if prefix:
6012        sql = f"{prefix} {sql}"
6013
6014    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
6015
6016
6017@t.overload
6018def maybe_copy(instance: None, copy: bool = True) -> None: ...
6019
6020
6021@t.overload
6022def maybe_copy(instance: E, copy: bool = True) -> E: ...
6023
6024
6025def maybe_copy(instance, copy=True):
6026    return instance.copy() if copy and instance else instance
6027
6028
6029def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
6030    """Generate a textual representation of an Expression tree"""
6031    indent = "\n" + ("  " * (level + 1))
6032    delim = f",{indent}"
6033
6034    if isinstance(node, Expression):
6035        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
6036
6037        if (node.type or verbose) and not isinstance(node, DataType):
6038            args["_type"] = node.type
6039        if node.comments or verbose:
6040            args["_comments"] = node.comments
6041
6042        if verbose:
6043            args["_id"] = id(node)
6044
6045        # Inline leaves for a more compact representation
6046        if node.is_leaf():
6047            indent = ""
6048            delim = ", "
6049
6050        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
6051        return f"{node.__class__.__name__}({indent}{items})"
6052
6053    if isinstance(node, list):
6054        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
6055        items = f"{indent}{items}" if items else ""
6056        return f"[{items}]"
6057
6058    # Indent multiline strings to match the current level
6059    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
6060
6061
6062def _is_wrong_expression(expression, into):
6063    return isinstance(expression, Expression) and not isinstance(expression, into)
6064
6065
6066def _apply_builder(
6067    expression,
6068    instance,
6069    arg,
6070    copy=True,
6071    prefix=None,
6072    into=None,
6073    dialect=None,
6074    into_arg="this",
6075    **opts,
6076):
6077    if _is_wrong_expression(expression, into):
6078        expression = into(**{into_arg: expression})
6079    instance = maybe_copy(instance, copy)
6080    expression = maybe_parse(
6081        sql_or_expression=expression,
6082        prefix=prefix,
6083        into=into,
6084        dialect=dialect,
6085        **opts,
6086    )
6087    instance.set(arg, expression)
6088    return instance
6089
6090
6091def _apply_child_list_builder(
6092    *expressions,
6093    instance,
6094    arg,
6095    append=True,
6096    copy=True,
6097    prefix=None,
6098    into=None,
6099    dialect=None,
6100    properties=None,
6101    **opts,
6102):
6103    instance = maybe_copy(instance, copy)
6104    parsed = []
6105    for expression in expressions:
6106        if expression is not None:
6107            if _is_wrong_expression(expression, into):
6108                expression = into(expressions=[expression])
6109
6110            expression = maybe_parse(
6111                expression,
6112                into=into,
6113                dialect=dialect,
6114                prefix=prefix,
6115                **opts,
6116            )
6117            parsed.extend(expression.expressions)
6118
6119    existing = instance.args.get(arg)
6120    if append and existing:
6121        parsed = existing.expressions + parsed
6122
6123    child = into(expressions=parsed)
6124    for k, v in (properties or {}).items():
6125        child.set(k, v)
6126    instance.set(arg, child)
6127
6128    return instance
6129
6130
6131def _apply_list_builder(
6132    *expressions,
6133    instance,
6134    arg,
6135    append=True,
6136    copy=True,
6137    prefix=None,
6138    into=None,
6139    dialect=None,
6140    **opts,
6141):
6142    inst = maybe_copy(instance, copy)
6143
6144    expressions = [
6145        maybe_parse(
6146            sql_or_expression=expression,
6147            into=into,
6148            prefix=prefix,
6149            dialect=dialect,
6150            **opts,
6151        )
6152        for expression in expressions
6153        if expression is not None
6154    ]
6155
6156    existing_expressions = inst.args.get(arg)
6157    if append and existing_expressions:
6158        expressions = existing_expressions + expressions
6159
6160    inst.set(arg, expressions)
6161    return inst
6162
6163
6164def _apply_conjunction_builder(
6165    *expressions,
6166    instance,
6167    arg,
6168    into=None,
6169    append=True,
6170    copy=True,
6171    dialect=None,
6172    **opts,
6173):
6174    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6175    if not expressions:
6176        return instance
6177
6178    inst = maybe_copy(instance, copy)
6179
6180    existing = inst.args.get(arg)
6181    if append and existing is not None:
6182        expressions = [existing.this if into else existing] + list(expressions)
6183
6184    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6185
6186    inst.set(arg, into(this=node) if into else node)
6187    return inst
6188
6189
6190def _apply_cte_builder(
6191    instance: E,
6192    alias: ExpOrStr,
6193    as_: ExpOrStr,
6194    recursive: t.Optional[bool] = None,
6195    append: bool = True,
6196    dialect: DialectType = None,
6197    copy: bool = True,
6198    **opts,
6199) -> E:
6200    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6201    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6202    cte = CTE(this=as_expression, alias=alias_expression)
6203    return _apply_child_list_builder(
6204        cte,
6205        instance=instance,
6206        arg="with",
6207        append=append,
6208        copy=copy,
6209        into=With,
6210        properties={"recursive": recursive or False},
6211    )
6212
6213
6214def _combine(
6215    expressions: t.Sequence[t.Optional[ExpOrStr]],
6216    operator: t.Type[Connector],
6217    dialect: DialectType = None,
6218    copy: bool = True,
6219    **opts,
6220) -> Expression:
6221    conditions = [
6222        condition(expression, dialect=dialect, copy=copy, **opts)
6223        for expression in expressions
6224        if expression is not None
6225    ]
6226
6227    this, *rest = conditions
6228    if rest:
6229        this = _wrap(this, Connector)
6230    for expression in rest:
6231        this = operator(this=this, expression=_wrap(expression, Connector))
6232
6233    return this
6234
6235
6236def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6237    return Paren(this=expression) if isinstance(expression, kind) else expression
6238
6239
6240def union(
6241    left: ExpOrStr,
6242    right: ExpOrStr,
6243    distinct: bool = True,
6244    dialect: DialectType = None,
6245    copy: bool = True,
6246    **opts,
6247) -> Union:
6248    """
6249    Initializes a syntax tree from one UNION expression.
6250
6251    Example:
6252        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6253        'SELECT * FROM foo UNION SELECT * FROM bla'
6254
6255    Args:
6256        left: the SQL code string corresponding to the left-hand side.
6257            If an `Expression` instance is passed, it will be used as-is.
6258        right: the SQL code string corresponding to the right-hand side.
6259            If an `Expression` instance is passed, it will be used as-is.
6260        distinct: set the DISTINCT flag if and only if this is true.
6261        dialect: the dialect used to parse the input expression.
6262        copy: whether to copy the expression.
6263        opts: other options to use to parse the input expressions.
6264
6265    Returns:
6266        The new Union instance.
6267    """
6268    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6269    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6270
6271    return Union(this=left, expression=right, distinct=distinct)
6272
6273
6274def intersect(
6275    left: ExpOrStr,
6276    right: ExpOrStr,
6277    distinct: bool = True,
6278    dialect: DialectType = None,
6279    copy: bool = True,
6280    **opts,
6281) -> Intersect:
6282    """
6283    Initializes a syntax tree from one INTERSECT expression.
6284
6285    Example:
6286        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6287        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6288
6289    Args:
6290        left: the SQL code string corresponding to the left-hand side.
6291            If an `Expression` instance is passed, it will be used as-is.
6292        right: the SQL code string corresponding to the right-hand side.
6293            If an `Expression` instance is passed, it will be used as-is.
6294        distinct: set the DISTINCT flag if and only if this is true.
6295        dialect: the dialect used to parse the input expression.
6296        copy: whether to copy the expression.
6297        opts: other options to use to parse the input expressions.
6298
6299    Returns:
6300        The new Intersect instance.
6301    """
6302    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6303    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6304
6305    return Intersect(this=left, expression=right, distinct=distinct)
6306
6307
6308def except_(
6309    left: ExpOrStr,
6310    right: ExpOrStr,
6311    distinct: bool = True,
6312    dialect: DialectType = None,
6313    copy: bool = True,
6314    **opts,
6315) -> Except:
6316    """
6317    Initializes a syntax tree from one EXCEPT expression.
6318
6319    Example:
6320        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6321        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6322
6323    Args:
6324        left: the SQL code string corresponding to the left-hand side.
6325            If an `Expression` instance is passed, it will be used as-is.
6326        right: the SQL code string corresponding to the right-hand side.
6327            If an `Expression` instance is passed, it will be used as-is.
6328        distinct: set the DISTINCT flag if and only if this is true.
6329        dialect: the dialect used to parse the input expression.
6330        copy: whether to copy the expression.
6331        opts: other options to use to parse the input expressions.
6332
6333    Returns:
6334        The new Except instance.
6335    """
6336    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6337    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6338
6339    return Except(this=left, expression=right, distinct=distinct)
6340
6341
6342def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6343    """
6344    Initializes a syntax tree from one or multiple SELECT expressions.
6345
6346    Example:
6347        >>> select("col1", "col2").from_("tbl").sql()
6348        'SELECT col1, col2 FROM tbl'
6349
6350    Args:
6351        *expressions: the SQL code string to parse as the expressions of a
6352            SELECT statement. If an Expression instance is passed, this is used as-is.
6353        dialect: the dialect used to parse the input expressions (in the case that an
6354            input expression is a SQL string).
6355        **opts: other options to use to parse the input expressions (again, in the case
6356            that an input expression is a SQL string).
6357
6358    Returns:
6359        Select: the syntax tree for the SELECT statement.
6360    """
6361    return Select().select(*expressions, dialect=dialect, **opts)
6362
6363
6364def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6365    """
6366    Initializes a syntax tree from a FROM expression.
6367
6368    Example:
6369        >>> from_("tbl").select("col1", "col2").sql()
6370        'SELECT col1, col2 FROM tbl'
6371
6372    Args:
6373        *expression: the SQL code string to parse as the FROM expressions of a
6374            SELECT statement. If an Expression instance is passed, this is used as-is.
6375        dialect: the dialect used to parse the input expression (in the case that the
6376            input expression is a SQL string).
6377        **opts: other options to use to parse the input expressions (again, in the case
6378            that the input expression is a SQL string).
6379
6380    Returns:
6381        Select: the syntax tree for the SELECT statement.
6382    """
6383    return Select().from_(expression, dialect=dialect, **opts)
6384
6385
6386def update(
6387    table: str | Table,
6388    properties: dict,
6389    where: t.Optional[ExpOrStr] = None,
6390    from_: t.Optional[ExpOrStr] = None,
6391    dialect: DialectType = None,
6392    **opts,
6393) -> Update:
6394    """
6395    Creates an update statement.
6396
6397    Example:
6398        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6399        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6400
6401    Args:
6402        *properties: dictionary of properties to set which are
6403            auto converted to sql objects eg None -> NULL
6404        where: sql conditional parsed into a WHERE statement
6405        from_: sql statement parsed into a FROM statement
6406        dialect: the dialect used to parse the input expressions.
6407        **opts: other options to use to parse the input expressions.
6408
6409    Returns:
6410        Update: the syntax tree for the UPDATE statement.
6411    """
6412    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6413    update_expr.set(
6414        "expressions",
6415        [
6416            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6417            for k, v in properties.items()
6418        ],
6419    )
6420    if from_:
6421        update_expr.set(
6422            "from",
6423            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6424        )
6425    if isinstance(where, Condition):
6426        where = Where(this=where)
6427    if where:
6428        update_expr.set(
6429            "where",
6430            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6431        )
6432    return update_expr
6433
6434
6435def delete(
6436    table: ExpOrStr,
6437    where: t.Optional[ExpOrStr] = None,
6438    returning: t.Optional[ExpOrStr] = None,
6439    dialect: DialectType = None,
6440    **opts,
6441) -> Delete:
6442    """
6443    Builds a delete statement.
6444
6445    Example:
6446        >>> delete("my_table", where="id > 1").sql()
6447        'DELETE FROM my_table WHERE id > 1'
6448
6449    Args:
6450        where: sql conditional parsed into a WHERE statement
6451        returning: sql conditional parsed into a RETURNING statement
6452        dialect: the dialect used to parse the input expressions.
6453        **opts: other options to use to parse the input expressions.
6454
6455    Returns:
6456        Delete: the syntax tree for the DELETE statement.
6457    """
6458    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6459    if where:
6460        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6461    if returning:
6462        delete_expr = t.cast(
6463            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6464        )
6465    return delete_expr
6466
6467
6468def insert(
6469    expression: ExpOrStr,
6470    into: ExpOrStr,
6471    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6472    overwrite: t.Optional[bool] = None,
6473    returning: t.Optional[ExpOrStr] = None,
6474    dialect: DialectType = None,
6475    copy: bool = True,
6476    **opts,
6477) -> Insert:
6478    """
6479    Builds an INSERT statement.
6480
6481    Example:
6482        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6483        'INSERT INTO tbl VALUES (1, 2, 3)'
6484
6485    Args:
6486        expression: the sql string or expression of the INSERT statement
6487        into: the tbl to insert data to.
6488        columns: optionally the table's column names.
6489        overwrite: whether to INSERT OVERWRITE or not.
6490        returning: sql conditional parsed into a RETURNING statement
6491        dialect: the dialect used to parse the input expressions.
6492        copy: whether to copy the expression.
6493        **opts: other options to use to parse the input expressions.
6494
6495    Returns:
6496        Insert: the syntax tree for the INSERT statement.
6497    """
6498    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6499    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6500
6501    if columns:
6502        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6503
6504    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6505
6506    if returning:
6507        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6508
6509    return insert
6510
6511
6512def condition(
6513    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6514) -> Condition:
6515    """
6516    Initialize a logical condition expression.
6517
6518    Example:
6519        >>> condition("x=1").sql()
6520        'x = 1'
6521
6522        This is helpful for composing larger logical syntax trees:
6523        >>> where = condition("x=1")
6524        >>> where = where.and_("y=1")
6525        >>> Select().from_("tbl").select("*").where(where).sql()
6526        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6527
6528    Args:
6529        *expression: the SQL code string to parse.
6530            If an Expression instance is passed, this is used as-is.
6531        dialect: the dialect used to parse the input expression (in the case that the
6532            input expression is a SQL string).
6533        copy: Whether to copy `expression` (only applies to expressions).
6534        **opts: other options to use to parse the input expressions (again, in the case
6535            that the input expression is a SQL string).
6536
6537    Returns:
6538        The new Condition instance
6539    """
6540    return maybe_parse(
6541        expression,
6542        into=Condition,
6543        dialect=dialect,
6544        copy=copy,
6545        **opts,
6546    )
6547
6548
6549def and_(
6550    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6551) -> Condition:
6552    """
6553    Combine multiple conditions with an AND logical operator.
6554
6555    Example:
6556        >>> and_("x=1", and_("y=1", "z=1")).sql()
6557        'x = 1 AND (y = 1 AND z = 1)'
6558
6559    Args:
6560        *expressions: the SQL code strings to parse.
6561            If an Expression instance is passed, this is used as-is.
6562        dialect: the dialect used to parse the input expression.
6563        copy: whether to copy `expressions` (only applies to Expressions).
6564        **opts: other options to use to parse the input expressions.
6565
6566    Returns:
6567        The new condition
6568    """
6569    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6570
6571
6572def or_(
6573    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6574) -> Condition:
6575    """
6576    Combine multiple conditions with an OR logical operator.
6577
6578    Example:
6579        >>> or_("x=1", or_("y=1", "z=1")).sql()
6580        'x = 1 OR (y = 1 OR z = 1)'
6581
6582    Args:
6583        *expressions: the SQL code strings to parse.
6584            If an Expression instance is passed, this is used as-is.
6585        dialect: the dialect used to parse the input expression.
6586        copy: whether to copy `expressions` (only applies to Expressions).
6587        **opts: other options to use to parse the input expressions.
6588
6589    Returns:
6590        The new condition
6591    """
6592    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6593
6594
6595def xor(
6596    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6597) -> Condition:
6598    """
6599    Combine multiple conditions with an XOR logical operator.
6600
6601    Example:
6602        >>> xor("x=1", xor("y=1", "z=1")).sql()
6603        'x = 1 XOR (y = 1 XOR z = 1)'
6604
6605    Args:
6606        *expressions: the SQL code strings to parse.
6607            If an Expression instance is passed, this is used as-is.
6608        dialect: the dialect used to parse the input expression.
6609        copy: whether to copy `expressions` (only applies to Expressions).
6610        **opts: other options to use to parse the input expressions.
6611
6612    Returns:
6613        The new condition
6614    """
6615    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, **opts))
6616
6617
6618def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6619    """
6620    Wrap a condition with a NOT operator.
6621
6622    Example:
6623        >>> not_("this_suit='black'").sql()
6624        "NOT this_suit = 'black'"
6625
6626    Args:
6627        expression: the SQL code string to parse.
6628            If an Expression instance is passed, this is used as-is.
6629        dialect: the dialect used to parse the input expression.
6630        copy: whether to copy the expression or not.
6631        **opts: other options to use to parse the input expressions.
6632
6633    Returns:
6634        The new condition.
6635    """
6636    this = condition(
6637        expression,
6638        dialect=dialect,
6639        copy=copy,
6640        **opts,
6641    )
6642    return Not(this=_wrap(this, Connector))
6643
6644
6645def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6646    """
6647    Wrap an expression in parentheses.
6648
6649    Example:
6650        >>> paren("5 + 3").sql()
6651        '(5 + 3)'
6652
6653    Args:
6654        expression: the SQL code string to parse.
6655            If an Expression instance is passed, this is used as-is.
6656        copy: whether to copy the expression or not.
6657
6658    Returns:
6659        The wrapped expression.
6660    """
6661    return Paren(this=maybe_parse(expression, copy=copy))
6662
6663
6664SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6665
6666
6667@t.overload
6668def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6669
6670
6671@t.overload
6672def to_identifier(
6673    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6674) -> Identifier: ...
6675
6676
6677def to_identifier(name, quoted=None, copy=True):
6678    """Builds an identifier.
6679
6680    Args:
6681        name: The name to turn into an identifier.
6682        quoted: Whether to force quote the identifier.
6683        copy: Whether to copy name if it's an Identifier.
6684
6685    Returns:
6686        The identifier ast node.
6687    """
6688
6689    if name is None:
6690        return None
6691
6692    if isinstance(name, Identifier):
6693        identifier = maybe_copy(name, copy)
6694    elif isinstance(name, str):
6695        identifier = Identifier(
6696            this=name,
6697            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6698        )
6699    else:
6700        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6701    return identifier
6702
6703
6704def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6705    """
6706    Parses a given string into an identifier.
6707
6708    Args:
6709        name: The name to parse into an identifier.
6710        dialect: The dialect to parse against.
6711
6712    Returns:
6713        The identifier ast node.
6714    """
6715    try:
6716        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6717    except ParseError:
6718        expression = to_identifier(name)
6719
6720    return expression
6721
6722
6723INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6724
6725
6726def to_interval(interval: str | Literal) -> Interval:
6727    """Builds an interval expression from a string like '1 day' or '5 months'."""
6728    if isinstance(interval, Literal):
6729        if not interval.is_string:
6730            raise ValueError("Invalid interval string.")
6731
6732        interval = interval.this
6733
6734    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6735
6736    if not interval_parts:
6737        raise ValueError("Invalid interval string.")
6738
6739    return Interval(
6740        this=Literal.string(interval_parts.group(1)),
6741        unit=Var(this=interval_parts.group(2).upper()),
6742    )
6743
6744
6745def to_table(
6746    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
6747) -> Table:
6748    """
6749    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6750    If a table is passed in then that table is returned.
6751
6752    Args:
6753        sql_path: a `[catalog].[schema].[table]` string.
6754        dialect: the source dialect according to which the table name will be parsed.
6755        copy: Whether to copy a table if it is passed in.
6756        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6757
6758    Returns:
6759        A table expression.
6760    """
6761    if isinstance(sql_path, Table):
6762        return maybe_copy(sql_path, copy=copy)
6763
6764    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6765
6766    for k, v in kwargs.items():
6767        table.set(k, v)
6768
6769    return table
6770
6771
6772def to_column(
6773    sql_path: str | Column,
6774    quoted: t.Optional[bool] = None,
6775    dialect: DialectType = None,
6776    copy: bool = True,
6777    **kwargs,
6778) -> Column:
6779    """
6780    Create a column from a `[table].[column]` sql path. Table is optional.
6781    If a column is passed in then that column is returned.
6782
6783    Args:
6784        sql_path: a `[table].[column]` string.
6785        quoted: Whether or not to force quote identifiers.
6786        dialect: the source dialect according to which the column name will be parsed.
6787        copy: Whether to copy a column if it is passed in.
6788        kwargs: the kwargs to instantiate the resulting `Column` expression with.
6789
6790    Returns:
6791        A column expression.
6792    """
6793    if isinstance(sql_path, Column):
6794        return maybe_copy(sql_path, copy=copy)
6795
6796    try:
6797        col = maybe_parse(sql_path, into=Column, dialect=dialect)
6798    except ParseError:
6799        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
6800
6801    for k, v in kwargs.items():
6802        col.set(k, v)
6803
6804    if quoted:
6805        for i in col.find_all(Identifier):
6806            i.set("quoted", True)
6807
6808    return col
6809
6810
6811def alias_(
6812    expression: ExpOrStr,
6813    alias: t.Optional[str | Identifier],
6814    table: bool | t.Sequence[str | Identifier] = False,
6815    quoted: t.Optional[bool] = None,
6816    dialect: DialectType = None,
6817    copy: bool = True,
6818    **opts,
6819):
6820    """Create an Alias expression.
6821
6822    Example:
6823        >>> alias_('foo', 'bar').sql()
6824        'foo AS bar'
6825
6826        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6827        '(SELECT 1, 2) AS bar(a, b)'
6828
6829    Args:
6830        expression: the SQL code strings to parse.
6831            If an Expression instance is passed, this is used as-is.
6832        alias: the alias name to use. If the name has
6833            special characters it is quoted.
6834        table: Whether to create a table alias, can also be a list of columns.
6835        quoted: whether to quote the alias
6836        dialect: the dialect used to parse the input expression.
6837        copy: Whether to copy the expression.
6838        **opts: other options to use to parse the input expressions.
6839
6840    Returns:
6841        Alias: the aliased expression
6842    """
6843    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6844    alias = to_identifier(alias, quoted=quoted)
6845
6846    if table:
6847        table_alias = TableAlias(this=alias)
6848        exp.set("alias", table_alias)
6849
6850        if not isinstance(table, bool):
6851            for column in table:
6852                table_alias.append("columns", to_identifier(column, quoted=quoted))
6853
6854        return exp
6855
6856    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6857    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6858    # for the complete Window expression.
6859    #
6860    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6861
6862    if "alias" in exp.arg_types and not isinstance(exp, Window):
6863        exp.set("alias", alias)
6864        return exp
6865    return Alias(this=exp, alias=alias)
6866
6867
6868def subquery(
6869    expression: ExpOrStr,
6870    alias: t.Optional[Identifier | str] = None,
6871    dialect: DialectType = None,
6872    **opts,
6873) -> Select:
6874    """
6875    Build a subquery expression that's selected from.
6876
6877    Example:
6878        >>> subquery('select x from tbl', 'bar').select('x').sql()
6879        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6880
6881    Args:
6882        expression: the SQL code strings to parse.
6883            If an Expression instance is passed, this is used as-is.
6884        alias: the alias name to use.
6885        dialect: the dialect used to parse the input expression.
6886        **opts: other options to use to parse the input expressions.
6887
6888    Returns:
6889        A new Select instance with the subquery expression included.
6890    """
6891
6892    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
6893    return Select().from_(expression, dialect=dialect, **opts)
6894
6895
6896@t.overload
6897def column(
6898    col: str | Identifier,
6899    table: t.Optional[str | Identifier] = None,
6900    db: t.Optional[str | Identifier] = None,
6901    catalog: t.Optional[str | Identifier] = None,
6902    *,
6903    fields: t.Collection[t.Union[str, Identifier]],
6904    quoted: t.Optional[bool] = None,
6905    copy: bool = True,
6906) -> Dot:
6907    pass
6908
6909
6910@t.overload
6911def column(
6912    col: str | Identifier,
6913    table: t.Optional[str | Identifier] = None,
6914    db: t.Optional[str | Identifier] = None,
6915    catalog: t.Optional[str | Identifier] = None,
6916    *,
6917    fields: Lit[None] = None,
6918    quoted: t.Optional[bool] = None,
6919    copy: bool = True,
6920) -> Column:
6921    pass
6922
6923
6924def column(
6925    col,
6926    table=None,
6927    db=None,
6928    catalog=None,
6929    *,
6930    fields=None,
6931    quoted=None,
6932    copy=True,
6933):
6934    """
6935    Build a Column.
6936
6937    Args:
6938        col: Column name.
6939        table: Table name.
6940        db: Database name.
6941        catalog: Catalog name.
6942        fields: Additional fields using dots.
6943        quoted: Whether to force quotes on the column's identifiers.
6944        copy: Whether to copy identifiers if passed in.
6945
6946    Returns:
6947        The new Column instance.
6948    """
6949    this = Column(
6950        this=to_identifier(col, quoted=quoted, copy=copy),
6951        table=to_identifier(table, quoted=quoted, copy=copy),
6952        db=to_identifier(db, quoted=quoted, copy=copy),
6953        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6954    )
6955
6956    if fields:
6957        this = Dot.build(
6958            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
6959        )
6960    return this
6961
6962
6963def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6964    """Cast an expression to a data type.
6965
6966    Example:
6967        >>> cast('x + 1', 'int').sql()
6968        'CAST(x + 1 AS INT)'
6969
6970    Args:
6971        expression: The expression to cast.
6972        to: The datatype to cast to.
6973        copy: Whether to copy the supplied expressions.
6974
6975    Returns:
6976        The new Cast instance.
6977    """
6978    expr = maybe_parse(expression, copy=copy, **opts)
6979    data_type = DataType.build(to, copy=copy, **opts)
6980
6981    if expr.is_type(data_type):
6982        return expr
6983
6984    expr = Cast(this=expr, to=data_type)
6985    expr.type = data_type
6986
6987    return expr
6988
6989
6990def table_(
6991    table: Identifier | str,
6992    db: t.Optional[Identifier | str] = None,
6993    catalog: t.Optional[Identifier | str] = None,
6994    quoted: t.Optional[bool] = None,
6995    alias: t.Optional[Identifier | str] = None,
6996) -> Table:
6997    """Build a Table.
6998
6999    Args:
7000        table: Table name.
7001        db: Database name.
7002        catalog: Catalog name.
7003        quote: Whether to force quotes on the table's identifiers.
7004        alias: Table's alias.
7005
7006    Returns:
7007        The new Table instance.
7008    """
7009    return Table(
7010        this=to_identifier(table, quoted=quoted) if table else None,
7011        db=to_identifier(db, quoted=quoted) if db else None,
7012        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
7013        alias=TableAlias(this=to_identifier(alias)) if alias else None,
7014    )
7015
7016
7017def values(
7018    values: t.Iterable[t.Tuple[t.Any, ...]],
7019    alias: t.Optional[str] = None,
7020    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
7021) -> Values:
7022    """Build VALUES statement.
7023
7024    Example:
7025        >>> values([(1, '2')]).sql()
7026        "VALUES (1, '2')"
7027
7028    Args:
7029        values: values statements that will be converted to SQL
7030        alias: optional alias
7031        columns: Optional list of ordered column names or ordered dictionary of column names to types.
7032         If either are provided then an alias is also required.
7033
7034    Returns:
7035        Values: the Values expression object
7036    """
7037    if columns and not alias:
7038        raise ValueError("Alias is required when providing columns")
7039
7040    return Values(
7041        expressions=[convert(tup) for tup in values],
7042        alias=(
7043            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
7044            if columns
7045            else (TableAlias(this=to_identifier(alias)) if alias else None)
7046        ),
7047    )
7048
7049
7050def var(name: t.Optional[ExpOrStr]) -> Var:
7051    """Build a SQL variable.
7052
7053    Example:
7054        >>> repr(var('x'))
7055        'Var(this=x)'
7056
7057        >>> repr(var(column('x', table='y')))
7058        'Var(this=x)'
7059
7060    Args:
7061        name: The name of the var or an expression who's name will become the var.
7062
7063    Returns:
7064        The new variable node.
7065    """
7066    if not name:
7067        raise ValueError("Cannot convert empty name into var.")
7068
7069    if isinstance(name, Expression):
7070        name = name.name
7071    return Var(this=name)
7072
7073
7074def rename_table(
7075    old_name: str | Table,
7076    new_name: str | Table,
7077    dialect: DialectType = None,
7078) -> AlterTable:
7079    """Build ALTER TABLE... RENAME... expression
7080
7081    Args:
7082        old_name: The old name of the table
7083        new_name: The new name of the table
7084        dialect: The dialect to parse the table.
7085
7086    Returns:
7087        Alter table expression
7088    """
7089    old_table = to_table(old_name, dialect=dialect)
7090    new_table = to_table(new_name, dialect=dialect)
7091    return AlterTable(
7092        this=old_table,
7093        actions=[
7094            RenameTable(this=new_table),
7095        ],
7096    )
7097
7098
7099def rename_column(
7100    table_name: str | Table,
7101    old_column_name: str | Column,
7102    new_column_name: str | Column,
7103    exists: t.Optional[bool] = None,
7104    dialect: DialectType = None,
7105) -> AlterTable:
7106    """Build ALTER TABLE... RENAME COLUMN... expression
7107
7108    Args:
7109        table_name: Name of the table
7110        old_column: The old name of the column
7111        new_column: The new name of the column
7112        exists: Whether to add the `IF EXISTS` clause
7113        dialect: The dialect to parse the table/column.
7114
7115    Returns:
7116        Alter table expression
7117    """
7118    table = to_table(table_name, dialect=dialect)
7119    old_column = to_column(old_column_name, dialect=dialect)
7120    new_column = to_column(new_column_name, dialect=dialect)
7121    return AlterTable(
7122        this=table,
7123        actions=[
7124            RenameColumn(this=old_column, to=new_column, exists=exists),
7125        ],
7126    )
7127
7128
7129def convert(value: t.Any, copy: bool = False) -> Expression:
7130    """Convert a python value into an expression object.
7131
7132    Raises an error if a conversion is not possible.
7133
7134    Args:
7135        value: A python object.
7136        copy: Whether to copy `value` (only applies to Expressions and collections).
7137
7138    Returns:
7139        The equivalent expression object.
7140    """
7141    if isinstance(value, Expression):
7142        return maybe_copy(value, copy)
7143    if isinstance(value, str):
7144        return Literal.string(value)
7145    if isinstance(value, bool):
7146        return Boolean(this=value)
7147    if value is None or (isinstance(value, float) and math.isnan(value)):
7148        return null()
7149    if isinstance(value, numbers.Number):
7150        return Literal.number(value)
7151    if isinstance(value, bytes):
7152        return HexString(this=value.hex())
7153    if isinstance(value, datetime.datetime):
7154        datetime_literal = Literal.string(
7155            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7156                sep=" "
7157            )
7158        )
7159        return TimeStrToTime(this=datetime_literal)
7160    if isinstance(value, datetime.date):
7161        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7162        return DateStrToDate(this=date_literal)
7163    if isinstance(value, tuple):
7164        if hasattr(value, "_fields"):
7165            return Struct(
7166                expressions=[
7167                    PropertyEQ(
7168                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7169                    )
7170                    for k in value._fields
7171                ]
7172            )
7173        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7174    if isinstance(value, list):
7175        return Array(expressions=[convert(v, copy=copy) for v in value])
7176    if isinstance(value, dict):
7177        return Map(
7178            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7179            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7180        )
7181    if hasattr(value, "__dict__"):
7182        return Struct(
7183            expressions=[
7184                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7185                for k, v in value.__dict__.items()
7186            ]
7187        )
7188    raise ValueError(f"Cannot convert {value}")
7189
7190
7191def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7192    """
7193    Replace children of an expression with the result of a lambda fun(child) -> exp.
7194    """
7195    for k, v in tuple(expression.args.items()):
7196        is_list_arg = type(v) is list
7197
7198        child_nodes = v if is_list_arg else [v]
7199        new_child_nodes = []
7200
7201        for cn in child_nodes:
7202            if isinstance(cn, Expression):
7203                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7204                    new_child_nodes.append(child_node)
7205            else:
7206                new_child_nodes.append(cn)
7207
7208        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7209
7210
7211def replace_tree(
7212    expression: Expression,
7213    fun: t.Callable,
7214    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7215) -> Expression:
7216    """
7217    Replace an entire tree with the result of function calls on each node.
7218
7219    This will be traversed in reverse dfs, so leaves first.
7220    If new nodes are created as a result of function calls, they will also be traversed.
7221    """
7222    stack = list(expression.dfs(prune=prune))
7223
7224    while stack:
7225        node = stack.pop()
7226        new_node = fun(node)
7227
7228        if new_node is not node:
7229            node.replace(new_node)
7230
7231            if isinstance(new_node, Expression):
7232                stack.append(new_node)
7233
7234    return new_node
7235
7236
7237def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7238    """
7239    Return all table names referenced through columns in an expression.
7240
7241    Example:
7242        >>> import sqlglot
7243        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7244        ['a', 'c']
7245
7246    Args:
7247        expression: expression to find table names.
7248        exclude: a table name to exclude
7249
7250    Returns:
7251        A list of unique names.
7252    """
7253    return {
7254        table
7255        for table in (column.table for column in expression.find_all(Column))
7256        if table and table != exclude
7257    }
7258
7259
7260def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7261    """Get the full name of a table as a string.
7262
7263    Args:
7264        table: Table expression node or string.
7265        dialect: The dialect to generate the table name for.
7266        identify: Determines when an identifier should be quoted. Possible values are:
7267            False (default): Never quote, except in cases where it's mandatory by the dialect.
7268            True: Always quote.
7269
7270    Examples:
7271        >>> from sqlglot import exp, parse_one
7272        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7273        'a.b.c'
7274
7275    Returns:
7276        The table name.
7277    """
7278
7279    table = maybe_parse(table, into=Table, dialect=dialect)
7280
7281    if not table:
7282        raise ValueError(f"Cannot parse {table}")
7283
7284    return ".".join(
7285        (
7286            part.sql(dialect=dialect, identify=True, copy=False)
7287            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7288            else part.name
7289        )
7290        for part in table.parts
7291    )
7292
7293
7294def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7295    """Returns a case normalized table name without quotes.
7296
7297    Args:
7298        table: the table to normalize
7299        dialect: the dialect to use for normalization rules
7300        copy: whether to copy the expression.
7301
7302    Examples:
7303        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7304        'A-B.c'
7305    """
7306    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7307
7308    return ".".join(
7309        p.name
7310        for p in normalize_identifiers(
7311            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7312        ).parts
7313    )
7314
7315
7316def replace_tables(
7317    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7318) -> E:
7319    """Replace all tables in expression according to the mapping.
7320
7321    Args:
7322        expression: expression node to be transformed and replaced.
7323        mapping: mapping of table names.
7324        dialect: the dialect of the mapping table
7325        copy: whether to copy the expression.
7326
7327    Examples:
7328        >>> from sqlglot import exp, parse_one
7329        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7330        'SELECT * FROM c /* a.b */'
7331
7332    Returns:
7333        The mapped expression.
7334    """
7335
7336    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7337
7338    def _replace_tables(node: Expression) -> Expression:
7339        if isinstance(node, Table):
7340            original = normalize_table_name(node, dialect=dialect)
7341            new_name = mapping.get(original)
7342
7343            if new_name:
7344                table = to_table(
7345                    new_name,
7346                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7347                    dialect=dialect,
7348                )
7349                table.add_comments([original])
7350                return table
7351        return node
7352
7353    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7354
7355
7356def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7357    """Replace placeholders in an expression.
7358
7359    Args:
7360        expression: expression node to be transformed and replaced.
7361        args: positional names that will substitute unnamed placeholders in the given order.
7362        kwargs: keyword arguments that will substitute named placeholders.
7363
7364    Examples:
7365        >>> from sqlglot import exp, parse_one
7366        >>> replace_placeholders(
7367        ...     parse_one("select * from :tbl where ? = ?"),
7368        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7369        ... ).sql()
7370        "SELECT * FROM foo WHERE str_col = 'b'"
7371
7372    Returns:
7373        The mapped expression.
7374    """
7375
7376    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7377        if isinstance(node, Placeholder):
7378            if node.this:
7379                new_name = kwargs.get(node.this)
7380                if new_name is not None:
7381                    return convert(new_name)
7382            else:
7383                try:
7384                    return convert(next(args))
7385                except StopIteration:
7386                    pass
7387        return node
7388
7389    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7390
7391
7392def expand(
7393    expression: Expression,
7394    sources: t.Dict[str, Query],
7395    dialect: DialectType = None,
7396    copy: bool = True,
7397) -> Expression:
7398    """Transforms an expression by expanding all referenced sources into subqueries.
7399
7400    Examples:
7401        >>> from sqlglot import parse_one
7402        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7403        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7404
7405        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7406        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7407
7408    Args:
7409        expression: The expression to expand.
7410        sources: A dictionary of name to Queries.
7411        dialect: The dialect of the sources dict.
7412        copy: Whether to copy the expression during transformation. Defaults to True.
7413
7414    Returns:
7415        The transformed expression.
7416    """
7417    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7418
7419    def _expand(node: Expression):
7420        if isinstance(node, Table):
7421            name = normalize_table_name(node, dialect=dialect)
7422            source = sources.get(name)
7423            if source:
7424                subquery = source.subquery(node.alias or name)
7425                subquery.comments = [f"source: {name}"]
7426                return subquery.transform(_expand, copy=False)
7427        return node
7428
7429    return expression.transform(_expand, copy=copy)
7430
7431
7432def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7433    """
7434    Returns a Func expression.
7435
7436    Examples:
7437        >>> func("abs", 5).sql()
7438        'ABS(5)'
7439
7440        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7441        'CAST(5 AS DOUBLE)'
7442
7443    Args:
7444        name: the name of the function to build.
7445        args: the args used to instantiate the function of interest.
7446        copy: whether to copy the argument expressions.
7447        dialect: the source dialect.
7448        kwargs: the kwargs used to instantiate the function of interest.
7449
7450    Note:
7451        The arguments `args` and `kwargs` are mutually exclusive.
7452
7453    Returns:
7454        An instance of the function of interest, or an anonymous function, if `name` doesn't
7455        correspond to an existing `sqlglot.expressions.Func` class.
7456    """
7457    if args and kwargs:
7458        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7459
7460    from sqlglot.dialects.dialect import Dialect
7461
7462    dialect = Dialect.get_or_raise(dialect)
7463
7464    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7465    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7466
7467    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7468    if constructor:
7469        if converted:
7470            if "dialect" in constructor.__code__.co_varnames:
7471                function = constructor(converted, dialect=dialect)
7472            else:
7473                function = constructor(converted)
7474        elif constructor.__name__ == "from_arg_list":
7475            function = constructor.__self__(**kwargs)  # type: ignore
7476        else:
7477            constructor = FUNCTION_BY_NAME.get(name.upper())
7478            if constructor:
7479                function = constructor(**kwargs)
7480            else:
7481                raise ValueError(
7482                    f"Unable to convert '{name}' into a Func. Either manually construct "
7483                    "the Func expression of interest or parse the function call."
7484                )
7485    else:
7486        kwargs = kwargs or {"expressions": converted}
7487        function = Anonymous(this=name, **kwargs)
7488
7489    for error_message in function.error_messages(converted):
7490        raise ValueError(error_message)
7491
7492    return function
7493
7494
7495def case(
7496    expression: t.Optional[ExpOrStr] = None,
7497    **opts,
7498) -> Case:
7499    """
7500    Initialize a CASE statement.
7501
7502    Example:
7503        case().when("a = 1", "foo").else_("bar")
7504
7505    Args:
7506        expression: Optionally, the input expression (not all dialects support this)
7507        **opts: Extra keyword arguments for parsing `expression`
7508    """
7509    if expression is not None:
7510        this = maybe_parse(expression, **opts)
7511    else:
7512        this = None
7513    return Case(this=this, ifs=[])
7514
7515
7516def array(
7517    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7518) -> Array:
7519    """
7520    Returns an array.
7521
7522    Examples:
7523        >>> array(1, 'x').sql()
7524        'ARRAY(1, x)'
7525
7526    Args:
7527        expressions: the expressions to add to the array.
7528        copy: whether to copy the argument expressions.
7529        dialect: the source dialect.
7530        kwargs: the kwargs used to instantiate the function of interest.
7531
7532    Returns:
7533        An array expression.
7534    """
7535    return Array(
7536        expressions=[
7537            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7538            for expression in expressions
7539        ]
7540    )
7541
7542
7543def tuple_(
7544    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7545) -> Tuple:
7546    """
7547    Returns an tuple.
7548
7549    Examples:
7550        >>> tuple_(1, 'x').sql()
7551        '(1, x)'
7552
7553    Args:
7554        expressions: the expressions to add to the tuple.
7555        copy: whether to copy the argument expressions.
7556        dialect: the source dialect.
7557        kwargs: the kwargs used to instantiate the function of interest.
7558
7559    Returns:
7560        A tuple expression.
7561    """
7562    return Tuple(
7563        expressions=[
7564            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7565            for expression in expressions
7566        ]
7567    )
7568
7569
7570def true() -> Boolean:
7571    """
7572    Returns a true Boolean expression.
7573    """
7574    return Boolean(this=True)
7575
7576
7577def false() -> Boolean:
7578    """
7579    Returns a false Boolean expression.
7580    """
7581    return Boolean(this=False)
7582
7583
7584def null() -> Null:
7585    """
7586    Returns a Null expression.
7587    """
7588    return Null()
7589
7590
7591NONNULL_CONSTANTS = (
7592    Literal,
7593    Boolean,
7594)
7595
7596CONSTANTS = (
7597    Literal,
7598    Boolean,
7599    Null,
7600)